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.
Hello Experts,
I have a table named Members and below is the data:
I want to get distinct count of pkey year wise. If a pkey is active from 2019 to 9999 or till 2019 or greater than 2019 then that pkey will be counted for year 2019 (For startDate 2019 we need to consider records having startdate year as 2019 or less than 2019).
Also I have DateDim (created using dax) for year 2018,2019,2020 and 2021. In my model there is no relationship defined between DateDim and Members table.
Also we have Year slicer in report which we want to pull from DateDim and based on the slicer selection grid should change.
Below is the expected output:
Can we achieve this using DAX measure?
Any help or suggestion would highly be appreciated.
Thanks
Solved! Go to Solution.
Hi @mayurwadhwani ,
I updated the formula in the original sample pbix file, please check whether it is ok.
Measure =
VAR _count =
COUNTROWS ( ALLSELECTED ( 'DateDim'[Date].[Year] ) )
VAR _count1 =
CALCULATE ( DISTINCTCOUNT ( 'DateDim'[Date].[Year] ), ALL ( 'DateDim' ) )
RETURN
IF (
_count = _count1,
CALCULATE ( DISTINCTCOUNT ( 'Members'[pkey] ) ),
CALCULATE (
[Total Count distinct pkey],
FILTER ( ALLSELECTED ( 'DateDim' ), 'DateDim'[Date] = MAX ( 'DateDim'[Date] ) )
)
)
Best Regards
Hi @mayurwadhwani ,
You can create a measure as below:
Total Count distinct pkey =
VAR _selyear =
SELECTEDVALUE ( 'DateDim'[Date].[Year] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Members'[pkey] ),
FILTER (
'Members',
YEAR ( 'Members'[StartDate] ) <= _selyear
&& YEAR ( 'Members'[EndDate] ) >= _selyear
)
)
Best Regards
Thanks @v-yiruan-msft for the response. But if we keep card visual and show total members irrespective of year. And if we select multiple year from slicer, it shows incorrect value. As SELECTEDVALUE function results blank with multiple selection of year slicer.
Tried to solve this using VALUES function but no luck.
Can we achieve this using DAX?
Thanks
Hi @mayurwadhwani ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
Best Regards
Hi @v-yiruan-msft ,
If we select all the year in slicer it should show as 8 distinct key. In pbix, its coming out to be 4. Its due to SELECTEDVALUE function. As multiple selection is returning blank.
Thanks
Hi @mayurwadhwani ,
I updated the formula in the original sample pbix file, please check whether it is ok.
Measure =
VAR _count =
COUNTROWS ( ALLSELECTED ( 'DateDim'[Date].[Year] ) )
VAR _count1 =
CALCULATE ( DISTINCTCOUNT ( 'DateDim'[Date].[Year] ), ALL ( 'DateDim' ) )
RETURN
IF (
_count = _count1,
CALCULATE ( DISTINCTCOUNT ( 'Members'[pkey] ) ),
CALCULATE (
[Total Count distinct pkey],
FILTER ( ALLSELECTED ( 'DateDim' ), 'DateDim'[Date] = MAX ( 'DateDim'[Date] ) )
)
)
Best Regards
@mayurwadhwani , refer if the blog or attcahed file can help
Thanks @amitchandak for the response. My problem statement seems bit different. Its not like counting days between start date and endate. It also including comparision with entire table.
Any other suggerstions to achieve expected output.
Thanks
Thanks @amitchandak for the response. My problem statement seems bit different. Its not like counting days between start date and endate. It also including comparision with entire table.
Any other suggerstions to achieve expected output.
Thanks
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |