topic Re: Measure Conditional in DAX Commands and Tips

Measure Conditional

Anonymous — Wed, 31 Jul 2019 13:19:21 GMT

Hello,

I need to calculate in the same measure the following problem:

I have prices for 2018 and 2019, and in the same measure, I need to calculate the quatity*prices for 2018 if the date is before 01-10-2019, after that, I need to calculate quantitty*prices 2019.

Can someone help me, please?

Thank you

Re: Measure Conditional

Mariusz — Wed, 31 Jul 2019 14:44:05 GMT

Hi Anonymous

Can you provide a data sample?

Best Regards,
Mariusz

Please feel free to connect with me.

Re: Measure Conditional

Anonymous — Wed, 31 Jul 2019 14:51:51 GMT

Hi Mariusz ,

I did it just right now.

I just did a lot of ifs with calculates...

IF(sum('table'[date]<DATE[yyyy;mm;dd);

(IF(SUM('Tabela Categoria'[CAT_2])=1;CALCULATE(SUM('Table Sales'[Sales Price]);'Table Sales'[Year]=2018);IF(SUM('Tabela Categoria'[CAT_2])=2;CALCULATE(SUM('Table Sales'[Sales Price]);'Table Sales'[Year]=2018);IF(SUM('Tabela Categoria'[CAT_2])=3;CALCULATE(SUM('Table Sales'[Sales Price]);'VTable Sales'[Year]=2018)))));(IF(SUM('Tabela Categoria'[CAT_2])=1;CALCULATE(SUM('Table Sales'[Sales Price]);'Table Sales'[Year]=2019);IF(SUM('Tabela Categoria'[CAT_2])=2;CALCULATE(SUM('Table Sales'[Sales Price]);'Table Sales'[Year]=2019);IF(SUM('Tabela Categoria'[CAT_2])=3;CALCULATE(SUM('Table Sales'[Sales Price]);'Table Sales'[Year]=2019)))))

Do you believe you know a easier way?

Thank you for your feedback.

Re: Measure Conditional

Anonymous — Wed, 31 Jul 2019 15:10:52 GMT

Asking Questions on Power BI Forum - https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: Measure Conditional

Cmcmahan — Wed, 31 Jul 2019 15:17:40 GMT

There definitely is an easier way, but it depends on how your data is modeled. Could you share a sample of the raw data with us so we can see how it is set up in your data table?

Also, if you could share a mockup of your desired visual as well, that would help too. It looks like you're trying to control and account for every possible category/year combination instead of letting your visual handle the context changes for you.

Re: Measure Conditional

Anonymous — Wed, 31 Jul 2019 15:45:13 GMT

Cmcmahan

I have a table Price with the following information(In this table I have different prices by group by the prices of 2019 only be applicable after 01.10.2019.

Lisbon|Destiny|Region|A|10|2018 | Date

Lisbon|Destiny|Region|B|20| 2018 | Date

Lisbon|Destiny|Region|C|30| 2018 | Date

Lisbon|Destiny|Region|A|15| 2019 | 01.10.2019

Lisbon|Destiny|Region|B|25| 2019 | 01.10.2019

Lisbon|Destiny|Region|C|35| 2019 | 01.10.2019

Table of buyer:

Lisbon|Destiny|Region|A|10|2018 |

Lisbon|Destiny|Region|B|20| 2018 |

Lisbon|Destiny|Region|C|30| 2018 |

Lisbon|Destiny|Region|A|15| 2019 |

Lisbon|Destiny|Region|B|25| 2019 |

Lisbon|Destiny|Region|C|35| 2019 |

Lisbon|Destiny|Region|A|15| 2019 | New Proposal

Lisbon|Destiny|Region|B|25| 2019 | New Proposal

Lisbon|Destiny|Region|C|35| 2019 | New Proposal

Lisbon|Destiny|Region|A|15| 2019 | Applicable

Lisbon|Destiny|Region|B|25| 2019 | Applicable

Lisbon|Destiny|Region|C|35| 2019 | Applicable

Table of Model Data:

Model | Group | CAT | Brand | Concatenate

123 | 1 | A | Apple | 123A

Category Table

CAT | CAT_2

A | 1

B | 2

C | 3

Table of Data

Date| Model|Sales Value | Buyer value

Relationships

Table of Data <--> Model Data

Model Data <--> Category Table

Category Table <-->Buyer Table

Category Table <-->Sales Table

I want my measure to calculate the price of 2018 for the table data until 01.10.2019,after that I want prices 2019.

thank you

The data is too confusing. hope you can understand :)

Re: Measure Conditional

Cmcmahan — Wed, 31 Jul 2019 16:23:16 GMT

This is very helpful.

In your original post, you said that you needed to multiply the sales price with the quantity. From the tables you shared, we can get a sales price for any given combination of Group/Date easily. How do you determine the quantity?

Re: Measure Conditional

Anonymous — Wed, 31 Jul 2019 16:27:47 GMT

Sorry, I was wrong in saying that I want to multiply. I have a table and I want to add a column in the table of the data(in terms of visualizations) to have prices of 2019.

I need a visualization with all the information in the table data and add the following columns:

Group | Buyer 2019 | Sale 2019 |

Re: Measure Conditional

Cmcmahan — Wed, 31 Jul 2019 19:23:53 GMT

So it seems like your data is set up in an almost completely useless way for PowerBI. Your best use of time is likely to normalize your data and set up unique indexes.

I'm guessing the intention is that Date in the Price table is a "Price First Effective" date, which makes sense, but then Buyers would need a Date Purchased column to make use of it. You could filter your visual by where Year = 2019, which solves filtering out 2018 data. However, I'm completely in the dark how you would want to calculate values for Buyer 2019 for given Group. Wouldn't it always be the same as Sale 2019?

Could you share your .pbix file? What you're asking might make more sense if we can see what you're trying to accomplish.