Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Selected Value and IF Else statement issue - Dynamic Calculations

Hi 

 

I have a below scenarion, I'm able to achive it but where i need to create multiple DAXs for each "Global/Region" values and its each "market" values as below example.

 

I have two global/region values and its assocaited market values.

 

Global region (ASIA) with its Markets (China, India, Pakistan, Asia-Total)
Global region (Africa) with its Markets (Nigeria, Ethiopia, Egypt, Africa-Total)

 

When even user select it should show its associate market values or when ever user select "Global/Region" it should show its "Global/Region" Here i have  a condition when user select ASIA he need to show ASIA total which should be "Asia-Total" market it should be ignore its Markets (China, India, Pakistan) Simillary same for Africa as i have created below screenshot for reference and DAXs i used.

 

 

First approach i did as below  

Created DAX for each global/region value and its markets its a manual effort if i have 50 markets then i need to do 50 DAXs which is not preferable 

 

Africa_total = CALCULATE(SUM(Countrys[Actual]),
FILTER(Countrys,'Countrys'[Country] = "Africa-Total"))


Simillary i did for all regions and markets 

 

Final DAX using as below

 

M_Selected = SWITCH(
SELECTEDVALUE('Countrys'[Region/Global]),"Africa",
SWITCH(SELECTEDVALUE(Countrys[Country]),
"Egypt",'Countrys'[Egypt_Total],
"Ethiopia",[Ethiopia_total],
"Nigeria",[Nigeria_Total],
"Africa-Total",[Africa_total],
[Africa_total]),
"Asia",
SWITCH(SELECTEDVALUE(Countrys[Country]),
"Asia-Total",[Asia-Total],
"China",[China_Total],
"India",[India_Total],
"Pakistan",[Pakistan_Total],
[Asia-Total]))

 

2nd Approach: - Planning to ignore all markets calculation

 

Created DYnamic market calculation measure: 

 

Measure_Market =
var _market=SELECTEDVALUE('Countrys'[Market])
return
CALCULATE(SUM(Countrys[Actual]),
'Countrys'[Market] = _market
)

 

M_Selected=
IF (SELECTEDVALUE('Countrys'[Region/Global]) IN {"Asia", "Asia-Total"}, [Asia-Total],
IF (SELECTEDVALUE('Countrys'[Region/Global]) IN {"Africa", "Africa-Total"}, [Africa-total],
[Measure_Market] ) )

 

Now i just created DAX for "Region/Total" for (Asia,Africa) and I created DAX for two makets (Asia-Total,Africa-Total)

remainings markets i'm planning to use the above Measure_Market

 

Problem :  Measure_Maket is showing blanks when ever i select makrets (China, India, Pakistan, Nigeria, Ethiopia, Egypt) in the report but working fine for "Globa/Region", How to acive the dynamic calculations for markets (China, India, Pakistan, Nigeria, Ethiopia, Egypt)

 

Need help

 

Thanks,

 

 

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi amitchandak 

     

    Sorry the below is also not working 

     

    how to handle the else part -

     

    Finaltest = SWITCH(SELECTEDVALUE(Countrys[Region/Global]),
    "Asia",     [Asia-Total],
    "Africa",[Africa-Total],
    "sum(Countrys[Actual]))
     
    Still working, but not working on else part
     
    Thanks for your reponse.
  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , is the Country table is joined with your fact or an independent table for the Measure slicer.?

     

    If it joined not sure about the need of a separate table

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

     

    measure slicer
    https://www.youtube.com/watch?v=b9352Vxuj-M
    https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
    https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi amitchandak 

       

      Its a single file, no joins associated with any tables.

      I have a single table with all coulmns country, market, and its measures.

       

      Thanks,

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi amitchandak 

       

      Please find the sample data below.

       

      Region/GlobalCountryActualTargetSort
      AsiaAsia-Total65156
      AsiaChina1111
      AsiaIndia1222
      AsiaPakistan1333
      AfricaAfrica-Total865416
      AfricaNigeria201212
      AfricaEthiopia211313
      AfricaEgypt221414

      .

       

      Slicer I'm using for the report I created Hieraricy for (Region/Global and Country) as below.

       

      DAXs

      Africa_total = CALCULATE(SUM(Countrys[Actual]),
      FILTER(Countrys,'Countrys'[Country] = "Africa-Total"))

       

      Asia-Total = CALCULATE(SUM(Countrys[Actual]),
      FILTER(Countrys,'Countrys'[Country] = "Asia-Total"))

       

      China_Total = CALCULATE(SUM(Countrys[Actual]),
      FILTER(Countrys,'Countrys'[Country] = "China"))

       

      Egypt_Total = CALCULATE(SUM(Countrys[Actual]),
      FILTER(Countrys,'Countrys'[Country] = "Egypt"))


      Ethiopia_total = CALCULATE(SUM(Countrys[Actual]),
      FILTER(Countrys,'Countrys'[Country] = "Ethiopia"))


      India_Total = CALCULATE(SUM(Countrys[Actual]),
      FILTER(Countrys,'Countrys'[Country] = "India"))


      Nigeria_Total = CALCULATE(SUM(Countrys[Actual]),
      FILTER(Countrys,'Countrys'[Country] = "Nigeria"))


      Pakistan_Total = CALCULATE(SUM(Countrys[Actual]),
      FILTER(Countrys,'Countrys'[Country] = "Pakistan"))


      M_Selected = SWITCH(
      SELECTEDVALUE('Countrys'[Region/Global]),"Africa",
      SWITCH(SELECTEDVALUE(Countrys[Country]),
      "Egypt",'Countrys'[Egypt_Total],
      "Ethiopia",[Ethiopia_total],
      "Nigeria",[Nigeria_Total],
      "Africa-Total",[Africa_total],
      [Africa_total]),
      "Asia",
      SWITCH(SELECTEDVALUE(Countrys[Country]),
      "Asia-Total",[Asia-Total],
      "China",[China_Total],
      "India",[India_Total],
      "Pakistan",[Pakistan_Total],
      [Asia-Total]))

       

       

      Thanks

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous 

     

    Can you help on the above issue, unable to execute false statement.

     

    Thanks,