Forum Discussion

DavePBI's avatar
DavePBI
Frequent Visitor
6 years ago
Solved

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!!

 

 

 

 

DAX Calculation
DAX SWITCH
Help me
  • az38's avatar
    az38
    6 years ago

    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..

6 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

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

    • DavePBI's avatar
      DavePBI
      Frequent Visitor
      6 years ago

      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.


       

      • az38's avatar
        az38
        Community Champion
        6 years ago

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