Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey,
I have 3 Tables in my model, which are DimUsers, DimDate and FactSales. A slicer is showing month names from DimDate.
I want to find out users that are created in selected month from slicer. Normally if cross direction relationship is active the count of user_ids will show users in FactSales in the selected month but I need all users even if they have not bought any items.
I need Users that have been signed up in the selected month from slicer. the DimUsers contains a column (created_at) showing the users's creation date.
Here is the DAX I wrote but its not working
Sign Up = CALCULATE([User Counts], FILTER('DimUsers', 'DimUsers'[created_at_DimDateKey] in RELATEDTABLE(DimDate)))
Any Ideas ?
Solved! Go to Solution.
@Pinoo39 , Create an inactive relation between date and created_at and try useuserelation. You might have to use crossfilter(none) for fact
Sign Up = CALCULATE([User Counts], userelation(DimDate[Date], 'DimUsers'[created_at]))
refer: https://www.youtube.com/watch?v=e6Y-l_JtCq4
Another way is
Sign Up = CALCULATE([User Counts],filter(DimUsers , DimUsers[created_at] in values(DimDate[Date])))
or
Sign Up = CALCULATE([User Counts],filter(DimUsers , DimUsers[created_at] in allselected(DimDate[Date])))
Hi @Pinoo39
Try this
Sign Up =
CALCULATE (
DISTINCTCOUNT ( 'DimUsers'[User_ID] ),
'DimUsers'[created_at_DimDateKey] IN DISTINCT( DimDate[Date] )
)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@Pinoo39 , Create an inactive relation between date and created_at and try useuserelation. You might have to use crossfilter(none) for fact
Sign Up = CALCULATE([User Counts], userelation(DimDate[Date], 'DimUsers'[created_at]))
refer: https://www.youtube.com/watch?v=e6Y-l_JtCq4
Another way is
Sign Up = CALCULATE([User Counts],filter(DimUsers , DimUsers[created_at] in values(DimDate[Date])))
or
Sign Up = CALCULATE([User Counts],filter(DimUsers , DimUsers[created_at] in allselected(DimDate[Date])))
@Pinoo39 , you may try this
Sign Up = CALCULATE([User Counts], CALCULATETABLE(FactSales))
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |