Forum Discussion
Helper IVSingle Date filter for Multiple Columns and add Measures
Hi Kostas ,
Here is the .pbix that you can try it again: PBIX
Put the Date filter measure in the table visual filter and set up value as 1, put the Total measure in a card visual direcrtly and it should work fine.
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Community SupportHi Kostas ,
Based on your latest update, if I got it correctly, you can create a control measure like this and set the value as 1 to put it in the filter of the table visual:
Date filter =
VAR _mindate =
CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR _maxdate =
CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR _date1 =
SELECTEDVALUE ( 'Table'[Date1] )
VAR _date2 =
SELECTEDVALUE ( 'Table'[Date2] )
RETURN
IF ( _date1 < _mindate && _date2 < _maxdate, 1, 0 )If you want to count, just create a slimar measure and put it in the table visual:
Count =
VAR _mindate =
CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR _maxdate =
CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR _date1 =
SELECTEDVALUE ( 'Table'[Date1] )
VAR _date2 =
SELECTEDVALUE ( 'Table'[Date2] )
RETURN
SUMX ( 'Table', IF ( _date1 < _mindate && _date2 < _maxdate, 1, 0 ) )The result is like this:
Here is the sample file hopes to help you solve this issue, please try it: PBIX
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Helper IVhello v-yingjl
I can see the logic behind your code and thanks for the help.
Unfortunately when I am applying the date measure in the filter section as a way for the filter to show only the values with "1" it automatically only shows to be the advance filter and I cannot apply any values inside the box. So I cannot test it.
Any ideas why it does not let me apply any filters? When I also tried to place the measure into a slicer it did not let me to.
If you believe that is an application issue I will accept the solution as it seems that should work.
Thanks
Kostas
- v-yingjl
Hi Kostas ,
Have you tried my sample file attached previously? This measure will work fine in the table visual. However you cannot put it in a card visual filter. Even you put it in the filter, you cannot set its value as 0 or 1 in the card visual. The card visual is not supported to put this control measure in it currently.
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
- Kostas
Hey v-yingjl ,
I copied paste your measures into my pbix report.
I placed the open in period measure as you did into the table and tried to apply filter on the table the date measure.
When I am placing the date measure as filter I cannot change anything in the filtering or place the number ( 1 as you did).
See print screen.
I just re-installed to my machine the Power BI bu still nothing.
Thanks
- v-yingjl
Hi Kostas ,
I have modified the count measure like this:
Total = VAR _mindate = CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ) VAR _maxdate = CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ) VAR tab = SUMMARIZE ( 'Table', [ID], [Date1], [Date2], "Flag", VAR _date1 = 'Table'[Date1] VAR _date2 = 'Table'[Date2] RETURN IF ( _date1 < _mindate && _date2 < _maxdate, 1, 0 ) ) RETURN SUMX ( tab, [Flag] )When you use Date filter measure to set up value as 1 in the table visual, it will show the correct result in the card visual:
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.