Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am connecting a Power BI report to a tabular model. The model has a table (call it "Awards") that includes both current and historical data. The records have fields with "ValidFrom" and "ValidTo" dates. There is a parameter table (call it "DateParam") in the model that includes dates that can be selected. I want a table visual in Power BI to display only those records from Awards where the date selected in a DateParam slicer falls between the ValidFrom and ValidTo dates.
Example:
Award
id | ValidFrom | ValidTo |
1 | 2015-01-01 | 2016-01-01 |
2 | 2015-01-05 | 2016-01-01 |
1 | 2015-02-01 | 2015-10-01 |
DateParam
Date |
2015-01-02 |
2015-12-31 |
2016-03-01 |
NB: In my tabular model there is no relationship between Award and DateParam.
Desired outcome:
If I have a slicer for DateParam and select 2015-01-02 as the value, I want a table visual to display the id, ValidFrom, and ValidTo fields for only records 1 and 2 (record 3 should not be displayed because 2015-01-02 is not between record 3's ValidFrom and ValidTo).
Tried so far:
1. I have been able to create measures that count the records based on the DateParam slicer value, but I don't want a summarization of the records, I want a display of the records in the table visual.
2. I tried adding a calculated column to the tabular model called DisplayFilter with this DAX expression:
=if(HASONEVALUE(DateDim[date]),if(and( Values(DateDim[date]) >= Award_all[ValidFrom], Values(DateDim[date]) <= Award_all[ValidTo]),1,0),1)
Unfortunately, selecting a value from the DateParam slicer doesn't seem to change the initial value of DisplayFilter. I have read something suggesting that calculated columns are only calculated at processing time.
Are there other ways to apply the parameter table pattern to get this kind of dynamic filtering I want?
Hi @jayeckles,
We could try a measure to tag them. If we don't want them to display, we can filter them.
IfIn = IF ( HASONEVALUE ( DateParam[Date] ), IF ( MIN ( DateParam[Date] ) >= MIN ( Award[ValidFrom] ) && MIN ( DateParam[Date] ) <= MIN ( Award[ValidTo] ), 1, 0 ), 9 )
Why can we use "MIN" (or MAX) here? One row is a single record in the table visual in this scenario.
Best Regards!
Dale
I'll give this a try when I get a chance. Do you recommend creating the measure in SSAS, in PBI, or do you think it matters?
Hi @jayeckles,
We should use this measure in PBI.
Best Regards!
Dale
This did end up meeting the requirements specifically as I stated them, which is to say that it allows me to filter a table.
Unfortunatley, the table was just a specific instance of a more general requirement, and that is to be able to filter more or less any visualization based on the date I select.
For example, say I want a card that will count the award records that are displayed in my filtered table. Or, I want the sum of award amounts from the records displayed in the filtered table.
My expectation was that once I had a filter flag (your IfIn measure), I could then use that as a page level filter. Alas, I cannot (perhaps because a measure can't be used as a page level filter).
I'm really looking for a general solution to how to select an "as of" date for a report that is driven by a tabular model with historical data. I am open to other ways of designing the tabular model itself.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |