Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

External file filtered by STAR Schema Dimensions

Hi everybody,

 

I'm struggling with the some interaction on my dashboard, probably because of my lack of experience in Power BI DAX & Modeling.

 

I have simplified my data model to keep only interesting tables in this case. There is a Fact Table with all the values, and many dimensions tables linked to it (like a STAR schema). In this case I only keep the Univers_dimension, Date_dimensions and Client_dimensions. I also have an external excel file which is containing CRM data.

  • Univers_dimension and Fact_table are linked through the dimension univers_id

  • Date_dimensions is linked to the Fact_table through the dimension date_dt

  • Client_dimension is linked to the Fact_table through the dimension id_client

  • Client_dimension is linked to the CRM through the dimension id_client/id_marketing
  • Client_dimension has an inactive linked with the Date_dimension

  • CRM  has an inactive linked with the Date_dimension 

id_client values in the Client_dimension are unique.

id_marketing in the CRM is not unique : one client can bet multiple times.

 

What I would like to do: I would like to be able to slice my CRM data (stakes metric for example) by all the dimensions that I have in my star schema. 

 

Example - if my date slicer (from date_dimension) is between the 13th and 14th of December :

From the CRM data, I know that the client "1111" bets 10 dollars the 14th of December.

The client "2333" bets 1$ the 13th and 5$ the 14th of December => 6$ over the time range.

 

From the links between Client_dimension>Fact_table>Univers_dimension, I know that the client "1111" has an account open in SPORT while the client "2333" is associated to POKER. 

 

So if I filter by the dimension univers_opening I should be able to see the split of the stakes (between POKER and SPORT). But it's not working at all. First, I though it was because the date_dt (Fact_table) was not in the time range selected (date_dt from Date_dimension). But even if the date slicer starts the 12th of December, nothing is happening.

 

Here is my formula :

CRM stakes = 
CALCULATE(
SUM(crm[stakes]),
USERELATIONSHIP(crm[activity_date],date_dimension[date_dt])
)

 

I attached a sample of my PBI : https://we.tl/t-xUOWuYz8NJ

 

Baguettes_0-1643192898957.png

 

Thank you in advance for your help!

 

EDIT : in some cases, it is possible to find the same id_client in the Fact_table. For those cases, only the one with the most recent date should be considered.

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

Hi @Anonymous ,

I updated your sample pbix file(see attachment), please check whether it can get your expected result.

CRM stakes = 
VAR _allseluniv =
    ALLSELECTED ( 'univers_dimension'[univers_opening] )
VAR _selclient =
    CALCULATETABLE (
        VALUES ( 'fact_table'[id_client] ),
        FILTER (
            'univers_dimension',
            'univers_dimension'[univers_opening] IN _allseluniv
        )
    )
VAR _selmid =
    CALCULATETABLE (
        VALUES ( 'crm'[id_marketing] ),
        FILTER ( 'client_dimension', 'client_dimension'[id_client] IN _selclient )
    )
RETURN
    CALCULATE (
        SUM ( crm[stakes] ),
        FILTER ( 'crm', 'crm'[id_marketing] IN _selmid ),
        USERELATIONSHIP ( crm[activity_date], date_dimension[date_dt] )
    )

yingyinr_0-1643617539329.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yiruan-msft
Community Support
Community Support

Hi @Anonymous ,

I updated your sample pbix file(see attachment), please check whether it can get your expected result.

CRM stakes = 
VAR _allseluniv =
    ALLSELECTED ( 'univers_dimension'[univers_opening] )
VAR _selclient =
    CALCULATETABLE (
        VALUES ( 'fact_table'[id_client] ),
        FILTER (
            'univers_dimension',
            'univers_dimension'[univers_opening] IN _allseluniv
        )
    )
VAR _selmid =
    CALCULATETABLE (
        VALUES ( 'crm'[id_marketing] ),
        FILTER ( 'client_dimension', 'client_dimension'[id_client] IN _selclient )
    )
RETURN
    CALCULATE (
        SUM ( crm[stakes] ),
        FILTER ( 'crm', 'crm'[id_marketing] IN _selmid ),
        USERELATIONSHIP ( crm[activity_date], date_dimension[date_dt] )
    )

yingyinr_0-1643617539329.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

I'm still working on it but I haven't found a solution yet. Is my problem not well explained? Let me know if I can help you to understand it!

Anonymous
Not applicable

I'm still working on this issue. I modified my formula by the following one :

CRM stakes = 

CALCULATE(
SUM(crm[stakes]),
USERELATIONSHIP(crm[activity_date],date_dimension[date_dt]),
USERELATIONSHIP(crm[id_marketing], fact_table[id_client]),
CROSSFILTER(date_dimension[date_dt],fact_table[date_dt], None)
)

 

It is "almost" working, but only the first id_client record is associated to the dimension univers_opening (cf picture below). The other problem with this measure, it's that it doesn't handle the case where the same id_client is present in the Fact_table.

 

I though about applying this DAX measure on a calculated table groupby the id_client but it didn't work neither.

 

Any help would be more than appreciated,

 

Thank you 🙂

Capture.PNG

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors