topic Re: DAX Multiple column matches? Model attached in DAX Commands and Tips

DAX Multiple column matches? Model attached

carlenb — Wed, 18 Oct 2023 13:43:26 GMT

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.

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

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.

Thanks! Let me know if there are any questions.

Re: DAX Multiple column matches? Model attached

Greg_Deckler — Wed, 18 Oct 2023 14:16:51 GMT

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.

Re: DAX Multiple column matches? Model attached

carlenb — Thu, 19 Oct 2023 11:19:02 GMT

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.

Power BI

Re: DAX Multiple column matches? Model attached

Greg_Deckler — Thu, 19 Oct 2023 15:54:06 GMT

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

Re: DAX Multiple column matches? Model attached

carlenb — Thu, 19 Oct 2023 17:46:02 GMT

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=113584471339137695632&rtpof=true&sd=true

Expected output

Power BI output

Re: DAX Multiple column matches? Model attached

some_bih — Tue, 24 Oct 2023 22:43:17 GMT

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.

Re: DAX Multiple column matches? Model attached

carlenb — Wed, 25 Oct 2023 08:11:30 GMT

Thanks some_bih

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

Now the sum for each project is correct
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

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

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

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

Re: DAX Multiple column matches? Model attached

some_bih — Sun, 29 Oct 2023 18:35:15 GMT

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

)

Re: DAX Multiple column matches? Model attached

carlenb — Mon, 30 Oct 2023 09:33:10 GMT

Fantastic, thanks a lot some_bih for your patience 🙂