Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Guys,
I'm trying to work out why the DAX below is not working as I tried to say if the column [Purch.Doc] is blank or empty and the Profit Centre is F1001 return FOO and so on for each line.
So far like that only Return the Seg FOO for all of them.
Many thanks!!
SEG = SWITCH(TRUE(),
[Purch.Doc] = BLANK(), FORMAT([Profit Ctr] = "F1001", "FOO"),
[Purch.Doc] = BLANK(), FORMAT([Profit Ctr] = "G0003", "FOO"),
[Purch.Doc] = BLANK(), FORMAT([Profit Ctr] = "G0007", "C&H"),
[Purch.Doc] = BLANK(), FORMAT([Profit Ctr] = "G0009", "C&H"),
[Purch.Doc] = BLANK(), FORMAT([Profit Ctr] = "G1001", "C&H"),
[Purch.Doc] = BLANK(), FORMAT([Profit Ctr] = "L0002", "C&H"),
[Purch.Doc] = BLANK(), FORMAT([Profit Ctr] = "L0003", "FOO"),
BLANK())
Solved! Go to Solution.
Try this:
SEG =
SWITCH (
TRUE (),
[Purch.Doc] = BLANK() && [Profit Ctr] = "F1001", "FOO",
[Purch.Doc] = BLANK() && [Profit Ctr] = "G0003", "FOO",
[Purch.Doc] = BLANK() && [Profit Ctr] = "G0007", "C&H",
[Purch.Doc] = BLANK() && [Profit Ctr] = "G0009", "C&H",
[Purch.Doc] = BLANK() && [Profit Ctr] = "G1001", "C&H",
[Purch.Doc] = BLANK() && [Profit Ctr] = "L0002", "C&H",
[Purch.Doc] = BLANK() && [Profit Ctr] = "L0003", "FOO",
BLANK ()
)
Remember - it's considered best pratice to prefix any column name with the table name ( e.g. Table1[Purch.Doc] )
Best regards,
Martyn
If I answered your question, please help others by accepting it as a solution.
I think it should be something like this
SEG = SWITCH(TRUE(),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "F1001"), "FOO"),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "G0003"), "FOO"),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "G0007"), "C&H"),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "G0009"), "C&H"),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "G1001"), "C&H"),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "L0002"), "C&H"),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "L0003"), "FOO"),
BLANK())
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
I think it should be something like this
SEG = SWITCH(TRUE(),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "F1001"), "FOO"),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "G0003"), "FOO"),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "G0007"), "C&H"),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "G0009"), "C&H"),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "G1001"), "C&H"),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "L0002"), "C&H"),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "L0003"), "FOO"),
BLANK())
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Try this:
SEG =
SWITCH (
TRUE (),
[Purch.Doc] = BLANK() && [Profit Ctr] = "F1001", "FOO",
[Purch.Doc] = BLANK() && [Profit Ctr] = "G0003", "FOO",
[Purch.Doc] = BLANK() && [Profit Ctr] = "G0007", "C&H",
[Purch.Doc] = BLANK() && [Profit Ctr] = "G0009", "C&H",
[Purch.Doc] = BLANK() && [Profit Ctr] = "G1001", "C&H",
[Purch.Doc] = BLANK() && [Profit Ctr] = "L0002", "C&H",
[Purch.Doc] = BLANK() && [Profit Ctr] = "L0003", "FOO",
BLANK ()
)
Remember - it's considered best pratice to prefix any column name with the table name ( e.g. Table1[Purch.Doc] )
Best regards,
Martyn
If I answered your question, please help others by accepting it as a solution.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |