cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

DAX SWITCH TRUE - help needed

Hi community / DAX experts,

 

I need some help here on DAX statement that I cannot get working.

I have 2 tables :

1) VW_Dim_Relatie (= clients table) with a column SoortRelatie with values 00010, 00020, V1 and V3.  

2) VW_Fact_Dekking (= client value tavle) with a column IncassoProvisie_Jr with values that are empty, 0 and up to 10.000.  Based on this value I have created band groups connected to a specific value e.g. PB is between 0 and 100, etc.

 

I would like to add a colum to table VW_Dim_Relatie that is called "Client Segmentation". That needs to give values "0",  "PB", "PZ", "PG" for relation type 00010. And values "0", "ZB", "ZZ", "ZG" and "ZP" for relation type 00020. For relation types V1 and V3 it needs to give values "V1" and "V3". If empty field value then it needs to give "no status".   

 

Find below my DAX SWITCH TRUE () statement. I get message at bottom "the end of this input was reached". Can you please help and guide me what goes wrong here.... Thanks in advance!!

 

 

Screenshot.jpeg

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: DAX SWITCH TRUE - help needed

hi @DavePBI 

it depends on your business logic.

you should understand what exact value you want to use for define EK for each item.

maybe AVERAGE(), maybe SUM(). its all about task

I dont know whats the purpose of your report, what is data nature and so on..


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

6 REPLIES 6
Highlighted
Super User II
Super User II

Re: DAX SWITCH TRUE - help needed

Hi @DavePBI 

Its because of 2 problem:

1. delimiters. You should use only ";" or "," depends on locale. I bet ";" in your case will be correct

2. double [[. use the only [SoortRelatie], not [[SoortRelatie]]


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Highlighted
Frequent Visitor

Re: DAX SWITCH TRUE - help needed

Hi az38,

Thanks for getting back to me. Thanks for feedback. I made a mistake on sending incorrect image on that. See below the correct one.

As you can see issue is coming from fact that it cannot find column 'IncassoProvisie_Jr' due to fact that it is a secondary linked table.

 

So to expain further, I have 3 tables in this setting. VW_Dim_Relatie is linked 1 to * with a 2nd table and that second table is linked with also 1 to * connection to the 3rd table VW_Fact_Dekking that holds the column 'IncassoProvisie_Jr' that I need.

 

Any idea to overcome this? Thanks again for feedback.


Screenshot 2.jpeg

 

Highlighted
Super User II
Super User II

Re: DAX SWITCH TRUE - help needed

@DavePBI 

The issue is you have a few related rows from VW_Fact_Dekking table 

So, you have to define logic to what exactly related value you want to use. It can be MAX, MIN, SUM, etc.. or FIRSTNONBLANK. 

for example

EK =
var _IncassoProvisie_Jr = CALCULATE(FIRSTNONBLANK(VW_Fact_Dekking[IncassoProvisie_Jr]);1)
RETURN
SWITCH(TRUE();
VW_Dim_Relatie[SoortRelatie] = "00010" &&  _IncassoProvisie_Jr=0; "0";
VW_Dim_Relatie[SoortRelatie] = "00010" &&  _IncassoProvisie_Jr < 100; "PB";
"no_status"
)

 or

EK =
var _IncassoProvisie_Jr = CALCULATE(MIN(VW_Fact_Dekking[IncassoProvisie_Jr]))
RETURN
SWITCH(TRUE();
VW_Dim_Relatie[SoortRelatie] = "00010" &&  _IncassoProvisie_Jr=0; "0";
VW_Dim_Relatie[SoortRelatie] = "00010" &&  _IncassoProvisie_Jr < 100; "PB";
"no_status"
)

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Highlighted
Super User IV
Super User IV

Re: DAX SWITCH TRUE - help needed

If your table is linked to another via 1:*, then, first of all, there may be many values in the second table for a single row in the first table and secondly, to reach the "many" side of the relationship you have to use RELATEDTABLE.

I'd suggest you think well over again what you want to do because what you are trying to do right now is not doable.

Best
D


Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


Highlighted
Frequent Visitor

Re: DAX SWITCH TRUE - help needed

Hi az38,

Thanks so much for that. Really helpfulp it provides no errors anymore and returns the value.

It seems I am almost there, however when checking the label (like PB etc). It seems not to be correct always. Sometimes it should return a PZ but then gives a PG label...

Maybe this comes due to fact that I used SUM in the logic line. I did that as there are in _Incassoprovisie_Jr multiple rows with value within one single Relation, so that is why I took SUM. But maybe that is the issue and I should be usings something else instead?

 

Thanks again you are of great help....!!!

Highlighted
Super User II
Super User II

Re: DAX SWITCH TRUE - help needed

hi @DavePBI 

it depends on your business logic.

you should understand what exact value you want to use for define EK for each item.

maybe AVERAGE(), maybe SUM(). its all about task

I dont know whats the purpose of your report, what is data nature and so on..


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors