topic Except Function to get the list of values in DAX Commands and Tips

Except Function to get the list of values

ashishd — Sat, 03 Feb 2024 19:03:06 GMT

Hello,

Being a beginner in PowerBI and coming from Tableau background, I am stuck and unable to understand the below.

I have created the DAX calculated column to get the list of items present only on a given date and not present on another given date.

PO Added = VAR CD1 = SELECTCOLUMNS( CALCULATETABLE( VALUES('All Netstar Inventory'[PO#]), 'All Netstar Inventory'[Cut Date] = 'All Netstar Inventory'[CutDate_1], REMOVEFILTERS('All Netstar Inventory'[PO Removed]) ), "PO", 'All Netstar Inventory'[PO#] ) VAR CD2 = SELECTCOLUMNS( CALCULATETABLE( VALUES('All Netstar Inventory'[PO#]), 'All Netstar Inventory'[Cut Date] = 'All Netstar Inventory'[CutDate_2], REMOVEFILTERS('All Netstar Inventory'[PO Removed]) ), "PO", 'All Netstar Inventory'[PO#] ) RETURN EXCEPT(CD1, CD2)

1. If I use this field in a table chart, I am getting all the POs including a blank value rather than getting only the POs present in CD1.

2. If I create a count measure on top of this calculated column, the count comes correct.

3. Also, if run the above formula using the DAX Query view using the Evaluate command, there also it gives the list of POs only present in CD1.

So, I am not able to understand why 2 & 3 are working correctly but 1 is not working correctly. How can I fix 1 to get the list of POs correctly.

Thank you!

Re: Except Function to get the list of values

Greg_Deckler — Sun, 04 Feb 2024 00:18:45 GMT

ashishd Likely has to do with context. Can you provide sample data? Also, you can probably get rid of the blank by using DISTINCT instead of VALUES.

Re: Except Function to get the list of values

ashishd — Sun, 04 Feb 2024 03:56:10 GMT

Greg_Deckler Thank you for the response!

I am unable to attach the .pbix file here. Not sure why but here is the shared link of the file from GDrive:
https://drive.google.com/file/d/1hi2WP-GmiIz7mY7UPCMhnm6-gtvFfyrk/view?usp=sharing

Context:

Basically, the 2 cut dates are coming from the original column Cut date. I wanted to provide user the option to select any 2 dates from the available dates in the Cut date column and compare what POs have been added or removed. But I could not find how to give this functionality in PowerB.

I tried to use parameters but did not see any such option.

I tried to create 2 separate Calculated Tables for the dates and create 2 date slicers using these so that users can select the 2 dates. But the slicers do not impact the calculated column formula, so it was not dynamic. I then read at multiple places that calculated column are not impacted by slicers.
Then finally not finding any solution, I am trying to fix the 2 dates which are in the CutDate1 and CutDate2 columns and use them to get the Except function give the list of added/removed POs.

Regards,

Ashish

Re: Except Function to get the list of values

talespin — Sun, 04 Feb 2024 08:49:03 GMT

hi ashishd ,

You can use this for Calculated Column.

PO Added_ =

VAR _PONum = data[PO#]

VAR _CD1 = CALCULATE( MAX(data[PO#]) , REMOVEFILTERS(data), data[PO#] = _PONum, data[Cut Date] = data[CutDate_1])

VAR _CD2 = CALCULATE( MAX(data[PO#]), REMOVEFILTERS(data), data[PO#] = _PONum, data[Cut Date] = data[CutDate_2])

RETURN

IF( NOT(ISBLANK(_CD1)) && ISBLANK(_CD2), _PONum, BLANK())

Re: Except Function to get the list of values

talespin — Sun, 04 Feb 2024 08:57:23 GMT

hi ashishd ,

Sharing both Measure and Calculated column. My recommendation is to do this in Power Query.

M PO Added_ =

VAR _SUMM =

ADDCOLUMNS(

SUMMARIZE(

data,

data[PO#]

"CD1",

VAR _PONum = [PO#]

RETURN CALCULATE( MAX(data[PO#]) , REMOVEFILTERS(data), data[PO#] = _PONum, data[Cut Date] = data[CutDate_1]),

"CD2",

VAR _PONum = [PO#]

RETURN CALCULATE( MAX(data[PO#]), REMOVEFILTERS(data), data[PO#] = _PONum, data[Cut Date] = data[CutDate_2])

)

RETURN COUNTX(

_SUMM,

IF( NOT(ISBLANK([CD1])) && ISBLANK([CD2]), 1, BLANK() ))

Re: Except Function to get the list of values

ashishd — Sun, 04 Feb 2024 09:53:32 GMT

talespin Thank you, for the reply!

It is showing the correct list of POs added. If you have further few mins to answer my questions, I will be more grateful:

1. What do you mean by - do it in the Power Query?

I tried earlier to do this in Power Query but it was a lengthy effort - something like creating 2 smaller tables for POs in each date and then doing Full Outer join between them. Then using this new table and applying filter on each of the 2 PO columns like PO is blank, fetch the list as added or removed.

2. Why the calculated columns from my original formula which were using CalculatedTable function etc., was not showing the correct list using EXCEPT function? Being a newbie in PowerBI, I am trying to understand the workings.

Thank you!

Re: Except Function to get the list of values

talespin — Sun, 04 Feb 2024 11:00:52 GMT

Hi ashishd ,

1. You can do it in Power Query without joins. Add two columns for date checks, group and max. Use that as a filter. Sharing Power Query Steps below.

let
Source = Csv.Document(File.Contents("YourPath\data.csv"),[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers1" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"Promoted Headers1", "CD1", each if [Cut Date] = [CutDate_1] then 1 else 0),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "CD2", each if [Cut Date] = [CutDate_2] then 1 else 0),
#"Grouped Rows" = Table.Group(#"Added Custom1", {"PO#"}, {{"CD1", each List.Max([CD1]), type number}, {"CD2", each List.Max([CD2]), type number}, {"AllRows", each _, type table [#"PO#"=nullable text, Cut Date=nullable text, CutDate_1=nullable text, CutDate_2=nullable text, CD1=number, CD2=number]}}),
#"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"CD1", Int64.Type}, {"CD2", Int64.Type}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type", "Filter", each if [CD1] = 1 and [CD2] = 0 then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom2", each true),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Filter", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"CD1", "CD2"}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns", "AllRows", {"Cut Date", "CutDate_1", "CutDate_2"}, {"Cut Date", "CutDate_1", "CutDate_2"})
in
#"Expanded AllRows"

2. You have to understand Evaluation context ( ROW Context and Filter Context ) and context transition. Watch youtube video series from Alberto, watch them repeatedly until the concept sinks into your mind. These are building blocks of DAX.

In a calculated column you have row context. it will evaluate row by row. If you look at 2nd row and your calculate column CD1 returns PO# ás Cut Date = Cut Date_1 but CD2 doesn't and EXCEPT returns the PO# which is wrong as per the expected logic, since in the first row Cut Date = CutDate_2 this PO# shouldn't have been returned. Which is why your total on calculated column is wrong.

Re: Except Function to get the list of values

Greg_Deckler — Sun, 04 Feb 2024 14:33:26 GMT

ashishd Try this perhaps. PBIX is attached below signature.

Table = VAR __CD1 = SUMMARIZE( FILTER( 'data', [Cut Date] = [CutDate_1] ), [PO#] ) VAR __CD2 = SUMMARIZE( FILTER( 'data', [Cut Date] = [CutDate_2] ), [PO#] ) VAR __Result = EXCEPT( __CD1, __CD2 ) RETURN __Result

Re: Except Function to get the list of values

ashishd — Sun, 04 Feb 2024 15:23:45 GMT

Thank you Greg_Deckler - this works fine as well!

Re: Except Function to get the list of values

kmfitzy — Fri, 12 Jul 2024 03:17:42 GMT

Hi Greg_Deckler , hopefully I haven't broken any protocols by jumping in on this thread? I too have similar requirement, I have managed to derive the counts, howevever, what I really need to achieve is the actual list, in my case it's a list of employee names. I need to find the difference of a list of employees from this week compared to last week rather than the count. Is this possible do you think? Any advice would be great!
Regards,
Karin

Re: Except Function to get the list of values

Greg_Deckler — Fri, 12 Jul 2024 12:55:15 GMT

kmfitzy You might be better served by posting a new thread. I only noticed this because you @'d me. You can use CONCATENATEX across your EXCEPT table to get the list of names. Not sure if I can be more specific than that given the information at hand as of now.

Re: Except Function to get the list of values

kmfitzy — Sat, 13 Jul 2024 09:23:26 GMT

Hi Greg_Deckler ,
Thank you for taking the time to reply. Great suggestion to use CONCATENATEX, however, I shall post a new thread.
Regards,
Karin