Forum Discussion

Pinoo39's avatar
Pinoo39
Frequent Visitor
5 years ago
Solved

Filter DIM based on Slicer Values

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 ?

  • amitchandak's avatar
    amitchandak
    5 years ago

    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])))

3 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Pinoo39 , you may try this

    Sign Up = CALCULATE([User Counts], CALCULATETABLE(FactSales))

     

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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])))

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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