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

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.

Reply
carlenb
Advocate II
Advocate II

DAX Multiple column matches? Model attached

Hi,

 

I have a Sales table from 2019 to 2022 with sales volume, sales date and suppliers.

See model here:

https://drive.google.com/file/d/1ie-tNNGnjq2jvL54EP1bJF2xWKNMK3AU/view?usp=sharing

 

Problem

There are five columns in the 2019-2022 sales table defining which project each sale should be attributed to. One sale could be relevant for more than one project, see the picture below. I am not sure on how to set up the model and the relationships correctly given that there are multiple columns. One possibility could maybe be to join the five columns to one column, where each id is separated by a comma? But if so, I'm still not sure about the solution.  

 

carlenb_1-1697635967635.png

 

Data overview (crossed over are not relevant to the problem/output)

carlenb_0-1697635607591.png

 

Data

  • DimProject: each unique project id, project, sub-project
  • DimSupplier: Supplier name, supplier id
  • Sales 2019-2022: Sales volume, sales date, supplier id, article and then five (5) project id columns. 
  • Calendar 

Expected output

A graph with sales 2019-2022 (x-axis) and volume (y-axis), with possibility to filter each project, year and supplier. Example: The sales in 2019 for Project ID 22 (Project I and sub-project IB) is 12 466.  

 

carlenb_2-1697636583821.png

 

 

Thanks! Let me know if there are any questions. 

1 ACCEPTED SOLUTION

Hi @carlenb please try measure below (include possible filtering on column Projekt)

Sales Total Measure =
VAR __Table_sum =
    SUMMARIZE (
        'Sales2019-2022',
        [Sales date],
        [Supplier],
        [Supplier ID],
        [Article],
        "__SalesVolume", SUM ( [Sales volume] )
    )
VAR __Result_sum =
    SUMX ( __Table_sum, [__SalesVolume] )
RETURN
    IF (
        ISFILTERED ( DimProject[Projekt] ),
        __Result_sum,
        SUM ( 'Sales2019-2022'[Sales volume] )
    )

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






View solution in original post

8 REPLIES 8
some_bih
Super User
Super User

Hi @carlenb 

In test data, there is wrong sum for second listed project ID and please change MAX to SUM as marked below and you should get expected output.

 

some_bih_0-1698187116756.png

 

some_bih_0-1698187332382.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Thanks @some_bih 

 

I updated from MAX to SUM in the DAX and this is what happens: 

 

  1. Now the sum for each project is correct 
  2. However the total sum for all or multiple projects (for example if you select project I & project H the total sum will not be correct. When I use MAX as suggested by @Greg_Deckler it is the other way around, so the total sum for all projects is correct, but not for each project

See pictures below on what happens. Any ideas of how to get around this? 

 

From test data, the correct sum for all projects

carlenb_0-1698221194029.png

 

In Power BI using MAX for all projects (total is correct)

carlenb_1-1698221240803.png

 

In Power BI using SUM for all projects (total is not correct) 

carlenb_2-1698221320390.png

 

 

In Power BI using SUM for specific projects (total is correct) 

carlenb_3-1698221404735.png

 

 

 

 

 

Hi @carlenb please try measure below (include possible filtering on column Projekt)

Sales Total Measure =
VAR __Table_sum =
    SUMMARIZE (
        'Sales2019-2022',
        [Sales date],
        [Supplier],
        [Supplier ID],
        [Article],
        "__SalesVolume", SUM ( [Sales volume] )
    )
VAR __Result_sum =
    SUMX ( __Table_sum, [__SalesVolume] )
RETURN
    IF (
        ISFILTERED ( DimProject[Projekt] ),
        __Result_sum,
        SUM ( 'Sales2019-2022'[Sales volume] )
    )

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Fantastic, thanks a lot @some_bih for your patience 🙂 

Greg_Deckler
Super User
Super User

@carlenb Typically in these sorts of situations, you unpivot your columns so that your 5 columns become 2 columns, Attribute and Value. The Attibute becomes the column name and the values, well, the values.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thanks @Greg_Deckler 

 

I tried to unpivot and (almost) got it to work. It does return the correct value when I filter on projects, dates, suppliers etc. Yej! But if I leave it unfiltered it returns the wrong total sum, what am I missing? Please see below check in Excel (correct value) vs Power BI. 

 

  

carlenb_0-1697714137626.png

 

Power BI 

 

carlenb_1-1697714198215.png

 

carlenb_3-1697714289208.png

 

 

@carlenb Right, so after unpivoting your Sales Volume numbers are duplicated so a simple sum will return inflated values. You can fix this with a measure like below. I'm not 100% understanding of your data or why it is organized how it is so there may be other approaches but this will fix the sum for the unpivoted data as you have it:

Sales Total Measure = 
  VAR __Table = SUMMARIZE('Table', [Sales date], [Supplier], [Supplie ID], [Article], "__SalesVolume", MAX([Sales volumn]))
  VAR __Result = SUMX(__Table, [__SalesVolume])
RETURN
  __Result

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thanks @Greg_Deckler for the patience. I get it to work for for each project, supplier, year etc. But when I select multiple projects the sum is off. 

 

Example

The Power BI sales volume for all I projects is 27820 but it should be 38553, see picture below. I've linked the Power BI model and test data. Any ideas? 

 

Power BI: https://drive.google.com/file/d/1XDaL5TBVejoCArJo_-dUs-QxVaTrRliO/view?usp=sharing

 

Test data: https://docs.google.com/spreadsheets/d/1cJ-sHIwvC0pJjSYYznyVUzgGNrrF1mjn/edit?usp=sharing&ouid=11358...

 

Expected output 

carlenb_0-1697737165865.png

 

Power BI output 

carlenb_1-1697737211264.png

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors