Need help with DAX to display measures

[Anonymous]

I want to display calculated measures Tx1 - Tx6

I want these KAY1 - KAY6  to display bar charts  correctly in this same bar chart when the chiclet or slicer is selected. 

I have used the switch in a new column(STATION) to use multiple criteria(measures) against corresponding four fields as below :

STATION = SWITCH(BH1_Seismic[Member],"MTRA","KAY1","MTRC","KAY2","MTRE","KAY3","MTRA","KAY4","MTRC","KAY5","MTRF","KAY6",BH1_Seismic[Member])

The field has only MTRA, MTRC, MTRE and MTRF for the corresponding KAY1, KAY2, KAY3, KAY4, KAY5 and KAY6

 But my calculated column expression for STATION returned: KAY1, KAY2, KAY3 and KAY6... It didnt return KAY4 and KAY5.

Maybe there is a better option..Maybe you can help me with a DAX expression like ‘Lookup’ or an ‘if’ expression or a better work around.

See field relation below:

MTRA	KAY1
MTRC	KAY2
MTRE	KAY3
MTRA	KAY4
MTRC	KAY5
MTRF	KAY6

I intend to return KAY1, KAY2, KAY3, KAY4, KAY5 and KAY6

Please help

[v-chuncz-msft]

@Anonymous,

You have the wrong understanding of SWITCH Function. Forget it, just use one measure.

[Anonymous]

What measure should I use??..

Please assist ..

.What is the best approach to achieve this.. I have sent you the pbix file

[Anonymous]

I’ve created the tables and made the relationships..

Now With ‘SWITCH’, I can now display KAY1-KAY6

I have dragged STATION to legend.. Also tried dragging Mnemonics to legend.

But the data output is same for KAY1 and KAY4…and KAY2 and KAY5 which is not supposed to be so .

The displayed data are supposed to be different values as they are feeding differently from another table which has KMAT-1 and KMAT-2 .

See field relation below

MTRA	KMAT-1	KAY1
MTRC	KMAT-1	KAY2
MTRE	KMAT-1	KAY3
MTRA	KMAT-2	KAY4
MTRC	KMAT-2	KAY5
MTRF	KMAT-2	KAY6

Please I want to be able to display data for each selected site for each particular day.

I've tried different Switch statement but they still output same values for KAY1/KAY4 and KAY2/KAY5.

 Also If I select KAY2 and KAY5, same time, or KAY1 and KAY4 same time, The bar chart disappears.

Please assist

[v-chuncz-msft]

@Anonymous,

The data you provide are inconsistent. It makes us confused.

[Anonymous]

v-chuncz-msft,

I do appreciate your time.

I have been providing this same data here  and got solutions to similar issues. The data is straightforward and unambigous.

v-ljerr-msft, Simon_Hou-MSFT, Eric_Zhang, v-yulgu-msft, v-qiuyu-MSFT ,  smoupre etc

have all been providing me clear solutions which I followed up. Here they introduced me to SWITCH and various other workaround.

 I do appreciate your help and really want to learn DAX. I have been learning . 

[Anonymous]

Hi,

I've sent to your email the  other replica Pbix file which is now live, working, deployed and running perfectly:

I was successful in using the switch in this different project that has 2 one to one fields.

But this present  project has one to many fields..Thats why i made my initial submission in the thread for help. 

I was thinking  lookup or if or any other workaround  can help.. Actually the creation of the new tables you suggested helped in displaying the measures but it is not pulling the data appropriately.

 I only know Switch for now.

[v-chuncz-msft]

@Anonymous,

Simplify your model as much as possible and illustrate with dummy data. Otherwise, it's hard to understand your situation.

[Anonymous]

Thanks for your time.

I was successful in using the switch in a different project that has 2 one to one fields.

But This project has one to many fields..Thats why i was thinking  lookup or if or anyother workaorund  can help.

I've sent the other replica perfect project to your email. This is now live, working, deployed and running perfectly with same principle:

[v-chuncz-msft · Accepted solution]

@Anonymous,

Share the sample with less raw data and remove all unnecessary things. No calculated columns or measures, just explain your original requirement.

[v-chuncz-msft]

@Anonymous,

Your requirement is not very clear to us. Check if RELATEDTABLE Function helps.

[Anonymous]

Hi,

I've tried the RelatedTable but cant get the syntax for the Dax expression.

My switch expression would have worked but the  snag is it  returns only one of the MTRA(for KAY1) and MTRC(for KAY2).

I want it to also return the MTRA for KAY4 and MTRC for KAY5.

Maybe switch is only returning unique.

I've sent the pbix file to your email.

[v-chuncz-msft]

@Anonymous,

Add two tables and create relationships, then drag STATION to Legend.

[Anonymous]

Please, how do I  create a new table..

I have tried creating and got the below ...Data view-> New Table....

How do I create the other Table?? Do i need to add a formula :? Please What is the calculation to create/add the table??

Did you have a look at the PBix file?

[v-chuncz-msft]

@Anonymous,

Simply click Enter Data from the Home tab.

[Anonymous]

I’ve created the tables and made the relationships..

Now With ‘SWITCH’, I can now display KAY1-KAY6

I have dragged STATION to legend.. Also tried dragging Mnemonics to legend.

But the data output is same for KAY1 and KAY4…and KAY2 and KAY5 which is not supposed to be so .

The displayed data are supposed to be different values as they are feeding differently from another table which has KMAT-1 and KMAT-2 .

See field relation below

MTRA	KMAT-1	KAY1
MTRC	KMAT-1	KAY2
MTRE	KMAT-1	KAY3
MTRA	KMAT-2	KAY4
MTRC	KMAT-2	KAY5
MTRF	KMAT-2	KAY6

Please I want to be able to display data for each selected site for each particular day.

I've tried different Switch statement but they still output same values for KAY1/KAY4 and KAY2/KAY5.

Also If I select KAY2 and KAY5, same time, or KAY1 and KAY4 same time, The bar chart disappears.

********************************************************************************************************

I have used the switch in a new column(STATION) :

STATION = SWITCH(BH1_Seismic[Member],"MTRA","KAY1","MTRC","KAY2","MTRE","KAY3","MTRA","KAY4","MTRC","KAY5","MTRF","KAY6",BH1_Seismic[Member])

See my Data display measure below:

************************

Measure =
 SWITCH( TRUE(),
   VALUES(BH1_Seismic[Member])= "KAY1", [KAY1],
   VALUES(BH1_Seismic[Member]) = "KAY2", [KAY2],
   VALUES(BH1_Seismic[Member]) = "KAY3", [KAY3],
    VALUES(BH1_Seismic[Member]) = "KAY4", [KAY4],
    VALUES(BH1_Seismic[Member]) = "KAY5", [KAY5],
    VALUES(BH1_Seismic[Member]) = "KAY6", [KAY6],
BLANK())

*********************************************************************

Please assist