Selected Value and IF Else statement issue - Dynamic Calculations

[ramshoney1225]

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,

[ramshoney1225]

Hi @Anonymous 

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

Thanks,

[ramshoney1225]

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]

@ramshoney1225 , 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/50...
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...

[ramshoney1225]

Hi @amitchandak 

Please find the sample data below.

Region/Global	Country	Actual	Target	Sort
Asia	Asia-Total	65	15	6
Asia	China	11	1	1
Asia	India	12	2	2
Asia	Pakistan	13	3	3
Africa	Africa-Total	86	54	16
Africa	Nigeria	20	12	12
Africa	Ethiopia	21	13	13
Africa	Egypt	22	14	14

.

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

[ramshoney1225]

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,