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 have an SQL table with contracts and the valid from (Vanaf Datum) and valid to dates of these contracts. I want to create a table in Power BI in which all records are shown that have at least one day in the selected period by my slicer. Currently, I have the following dashboard:
Now, this table only shows the records which have a Vanaf Datum in the selected slicer period from 1-3-2021 to 31-3-2021. However, I want my Power BI table to include all records (in red) that have at least one day in the selected filter period (in black)
How can I do this in Power BI?
Solved! Go to Solution.
Here's the measure to filter the rows:
// If you have a Dates table
// that is DISCONNECTED from
// the fact table...
[Show Contract] =
IF( ISINSCOPE( FT[ContractID] ),
var ContractStartDate =
SELECTEDVALUE( FT[Vanaf Datum] )
var ContractEndDate =
SELECTEDVALUE( FT[Tot Datum2] )
var StartDate = MIN( Dates[Vanaf Datum] )
var EndDate = MAX( Dates[Vanaf Datum] )
return
1 * NOT(
ContractEndDate < StartDate
||
EndDate < ContractStartDate
)
)
Here's the measure to filter the rows:
// If you have a Dates table
// that is DISCONNECTED from
// the fact table...
[Show Contract] =
IF( ISINSCOPE( FT[ContractID] ),
var ContractStartDate =
SELECTEDVALUE( FT[Vanaf Datum] )
var ContractEndDate =
SELECTEDVALUE( FT[Tot Datum2] )
var StartDate = MIN( Dates[Vanaf Datum] )
var EndDate = MAX( Dates[Vanaf Datum] )
return
1 * NOT(
ContractEndDate < StartDate
||
EndDate < ContractStartDate
)
)
This is marked as a Solution, but I think it is just a partial solution. How would you go about implementing this measure into a slicer? If I try doing that, Power BI won't allow it. It also does not like the "NOT" command, forcing me to remove it and reverse the greater/less than signs (not even sure that will work, but i'm hoping it will).
What is the next step to this solution, so that I can select a period (month) in a slicer and it knows which records to include? Amit's proposal below also doesn't work - PBI doesn't recognize the tables in the CALCULATE command when I try to implement.
Great! Exactly what I needed. Thank you very much.
Secondly, you have to disconnect the Date table from the fact table. Only then will you be able to calculate what you want. All you need is a measure that will return 1 for each row in the table in your first post that should be included and 0 for each that should be excluded. Then you filter the visual using this measure: show a row when the measure = 1. I'll show you the measure in a moment...
@Anonymous , Ffor this you need an independent date table and then try a measure like
measure =
var _max = maxx(ALLSELECTED('Date'),'Date'[Date])
var _min = minx(ALLSELECTED('Date'),'Date'[Date])
return
calculate(countrows(Table), filter('Table', ('Table'[Vanaf Datum] <=_max 'Table'[Vanaf Datum] >=Min ) ||('Table'[To Datum2] <=_max 'Table'[To Datum2] >=Min ) ))
Can you elaborate on how this measure will help in creating the desired table as I explained above?
@Anonymous
Does your model have a proper date table or do you take your dates straight from the fact table?
I have created a separate date table using the DAX expression
@Anonymous
Please have a look at this. It's an excerpt from the documentation of one of the time-intel functions.
In order to use any time intelligence calculation, you need a well-formed date table. The Date table must satisfy the following requirements:
I don't think your table conforms to this.
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 |
---|---|
47 | |
23 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
42 | |
39 | |
19 | |
19 |