daxquery depends on dayname and controlid

[susmitha]

depends upon dayname and controld_id as shown ijn the below screenshot ,need to write a dax querycan any one please help

[Anonymous]

Hi @susmitha ,

Thanks @BeaBF  for the quick reply and solution. Please allow me to make some additions:

(1) This is my test data.  

(2) We can create calculated columns.

TheDayName = FORMAT([batch_run_dt_ct],"dddd")

Controlid = SWITCH([TheDayName],
"Monday", 5,
"Sunday", 4,
"Saturday", 3,
1
)

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[susmitha]

hello @vtangile,

the requirement is when it's monday load it should show control id 5 and then show only that load

if its tues to friday controld id 1 ,saturday load then it control id 3 it should show only those loads in pbi report.

If you refre to the below screenshot,its showing all the control id's in the report.

[Anonymous · Accepted solution]

Hi @susmitha ,

As I can see in my screenshot, when the load is on Monday, it shows control ID 5, and if it's on Tuesday to Friday it controls ID 1, and on Saturday load, then it controls ID 3.

If you only need to control the ID to appear on the visual, then you only need to place the fields you want on the visual.

If I misunderstood what you meant, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[susmitha]

@BeaBF,

depends on batchdt or batch name need to have a calculated column

[BeaBF]

@susmitha can you share the pbix?

[susmitha]

sry won't be able to share pbix,but as mentioned in the earlier screenshot those are the sameple data

[BeaBF]

@susmitha ok, I can't reproduce the table with a screen. If you want help, at least paste the data as if it were an Excel, thanks.

BBF

[susmitha]

@BeaBF,instead of dayname can we use run_dt.in pbi report we dont have dayname column

the requriement is like if its monday control_id should be 5.

[BeaBF]

@susmitha do you have sample data of power bi? on which i can calculate the correct column

BBF

[BeaBF]

@susmitha Hi!

Controlid = SWITCH([TheDayName],
"Monday", "5",
"Sunday", "4",
"Saturday", "3",
"1"
)

BBF