The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hello All,
I am new to Power Bi.
I have small dax calculation. etc
I need if then else like below.
I created a table in power bi with these values Phone,Tablet and put in slicer
and I have region values coming from Region table
Now my requirement is
if Region='APAC' then it has to check another conditions
then if Slicervalue= 'Phone' then the calculation like sum(Phonesales),
then if Slicervalue= 'Tablet' then the calculation like sum(Tabletsales)
else if Region ='EMEA'
Slicervalue= 'Phone' then the calculation like sum(Phonesales),
Slicervalue= 'Tablet' then the calculation like sum(Tabletsales) ....
not sure how to write this in dax.
Could any one please help
Solved! Go to Solution.
Hi @Anonymous ,
Here's my solution.
1.Create a type table by entering data.
2.Create a measure to get the different kind of sales.
Sales = SWITCH(SELECTEDVALUE('Table (2)'[Type]),"Phone",SUM('Table'[PhoneSales]),"Tablet",SUM('Table'[TabletSales]))
When 'Phone' is selected,
When 'Tablet' is selected,
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Isn't the region Table linked to the sales table? If not, try:
_measure =
VAR _Device =
SELECTEDVALUE ( DeviceTable[Device] )
VAR _Region =
SELECTEDVALUE ( RegionTable[Region] )
RETURN
SWITCH (
TRUE (),
AND ( _Region = "APAC", _Device = "Phone" ), SUM ( Table[PhoneSales] ),
AND ( _Region = "APAC", _Device = "Tablet" ), SUM ( Table[TabletSales] ),
AND ( _Region = "EMEA", _Device = "Phone" ), SUM ( Table[PhoneSales] ),
AND ( _Region = "EMEA", _Device = "Tablet" ), SUM ( Table[TabletSales] )
)
But bear in mind that unless there is a relationship between the region table and the sales table, you are going to get the same sales result by device type for both regions.
If there is a relationship between the region table and the sales table, all you need is:
_measure =
SWITCH (
SELECTEDVALUE ( DeviceTable[Device] ),
"Phone" , SUM ( Table[PhoneSales] ),
"Tablet" , SUM ( Table[TabletSales] )
)
It would help if you provided a depiction fo how the model is setup and how you expect to show the data in the visual
Proud to be a Super User!
Paul on Linkedin.
@Anonymous Here are some examples. Your requirements are not very clear.
Measure =
VAR __SlicerValue = SELECTEDVALUE('Table'[Column])
RETURN
IF(
MAX('Table'[Region]) = "APAC",
SWITCH(__SlicerValue,
"Phone",SUM('Table'[Phonesales]),
"Tablet",SUM('Table'[Tabletsales]),
),
SWITCH(__SlicerValue,
"Phone",SUM('Table'[Phonesales]),
"Tablet",SUM('Table'[Tabletsales]),
)
)
Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
It depends on how many regions you will need. If just 2 then you could do it with IF but it would probably be neater using a SWITCH statement
New Measure = SWITCH( TRUE(),
SELECTEDVALUE( 'Regions'[Region[]) = "APAC",
SWITCH( TRUE(),
SELECTEDVALUE( 'Table'[Slicer value] = "Phone", SUM(Phonesales[Amount]),
SELECTEDVALUE( 'Table'[Slicer value] = "Table", SUM(Tabletsales[Amount])
),
SELECTEDVALUE( 'Regions'[Region[]) = "EMEA",
SWITCH( TRUE(),
SELECTEDVALUE( 'Table'[Slicer value] = "Phone", SUM(Phonesales[Amount]),
SELECTEDVALUE( 'Table'[Slicer value] = "Table", SUM(Tabletsales[Amount])
)
)
Hi @johnt75 ,Greg_Deckler and PaulDBrown,
This is my sample table data.
Region | PhoneSales | TabletSales |
APAC | 100 | 200 |
APAC | 150 | 250 |
EMEA | 80 | 90 |
EMEA | 30 | 40 |
I have region and sales coming from same table.
Now my requirement is I will put a slicer to end user which have values like phone ,Tablet
and put a filter on region also
So If user selects slicer value phone then it has to show phone sales
similarly tablet then tablet sales.
So my condtion would be like this
if Region='APAC' then it has to check another conditions
then if Slicervalue= 'Phone' then the calculation like sum(Phonesales),
then if Slicervalue= 'Tablet' then the calculation like sum(Tabletsales)
else if Region ='EMEA'
Slicervalue= 'Phone' then the calculation like sum(Phonesales),
Slicervalue= 'Tablet' then the calculation like sum(Tabletsales) ....
Basically I will show one measure at a time based on slicer selection
Hi @Anonymous ,
Here's my solution.
1.Create a type table by entering data.
2.Create a measure to get the different kind of sales.
Sales = SWITCH(SELECTEDVALUE('Table (2)'[Type]),"Phone",SUM('Table'[PhoneSales]),"Tablet",SUM('Table'[TabletSales]))
When 'Phone' is selected,
When 'Tablet' is selected,
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.