Hello!
I do not have the access in my position to modify the data model and relationships between tables so I am trying to find a workaround to solve this problem.
The measure I am trying to write will calculate the number of items in "pending" status (but there is no official pending status to use in the measure. The definition of pending is below).
Pending at month end = (all claims closed > month end) + (all claims without a close date that were reported prior <= month end)
I am using a table visualization and each row in the table is a measure that is calculating based on a different dimension table date value (Open Date, Close Date, etc.) so I am using a generic Date table ('Date'[Calendar Date]) to create a trend analysis across 15 months.
This is an example of the measure I am using to create a connection between the generic date table and the require date field but I am struggling to figure out how to build in the Pending items into this measure.
----------------
* Pending Claims between Dates =
var dt = ALLSELECTED('Claim Close Date'[Calendar Date])
var Result =
CALCULATE(
[Claim Total Claim Count],
GENERATE('Date'
,FILTER(
dt
,'Date'[Calendar Date] = [Calendar Date]
)
)
)
Return Result
----------------
I was given some advice that lead me to write this measure in another forum but I get an error message at the CrossFilter - "CrossFilter function can only use the two column references participating in a relationship."
* Pending Test =
CALCULATE(COUNTX(
FILTER('FACT Claim',
'FACT Claim'[Claim Reported Date] <=MAX(
'Date'[Calendar Date])
&& (ISBLANK('FACT Claim'[Claim Close Date])
|| 'FACT Claim'[Claim Close Date]>MAX('Date'[Calendar Date]))),
[Claim Number]),
CROSSFILTER('Date'[Calendar Date],'FACT Claim'[Claim Reported Date],None)
)
I am so new at writing measures in DAX that I don't even know where to start. My guess is that the second measure error message is telling me that my Date table is not connected to the FACT Claim table. So I THINK I need to find a way to create a join and I THINK that is what I was doing with the Generate function in the first measure.
I attached an image of the table visual where I need to get this measure to work and some sample data that hopefully helps to explain need better.
Thank you for ANY help you can give!
Hi Jodi,
Thanks for the file, however I presume this is not the structure of the data in the fact table. Not only is it not in a tabular layout, it also doesn't map to the measures you've written because they refer to columns called:
- Claim Reported Date
- Claim Close Date
- Claim Number
Please upload a sample Excel file that shows an extract of your data in a tabular layout in line with your Power Pivot model in Power BI. If required, create a data/calendar table as well and the relationships the way they are structured in your actual file so we can build in allowances for them in the measure.
Then provide an example of your desired result that maps to the sample data so I can see the connection between the data and what you're wanting to see.
It might sound like a lot of work, but the fact table really only needs to include a few examples (enough to cover all scenarios) and you can automatically create a calendar table from the fact table using the Data table > New tool on the Design tab of the Power Pivot ribbon.
From there we will be able to better understand and help you.
Mynda
