Except Function to get the list of values

[ashishd]

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!

[ashishd]

Thank you @Greg_Deckler  - this works fine as well!

[ashishd]

@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!

[talespin]

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.

[talespin]

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() ))

[talespin · Accepted solution]

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())

[Greg_Deckler]

@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.

[ashishd]

@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

[Greg_Deckler]

@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