drived value based on a dataset

[kaustubhgh]

Dear All,

I have a requiremnt where I need to hide the rows if atleaset one date is null for a given record number.

In below example, all 3 rows for Record Number 1001 needs to show the status as "Partial" in a new column if there is atleast one row where Received Date is null.

Or other logic is if count of quantity is greater than Received Quantity  for Record number 1001 then status as partial for record number 1001.

Record Number	Item	Quantity	Received Date	Received Quantity	Comments
1001	Pen	2		0	Hide
1001	Cooker	3	10/15/2024	3	Hide
1001	Ipad	1	10/13/2024	1	Hide
1002	Lamp	3	10/16/2024	3	Show
1002	Bulb	2	10/16/2024	2	Show
1003	Ipad	1	10/17/2024	1	Show

I have this dataset coming from the Import Query table.

Requesting your help to implement this logic.

[lbendlin · Accepted solution]

show =
VAR a =
    ADDCOLUMNS (
        VALUES ( 'Table'[Record Number] ),
        "nulls",
            VAR r = [Record Number]
            RETURN
                CALCULATE (
                    COUNTROWS ( ALLSELECTED ( 'Table' ) ),
                    'Table'[Record Number] = r,
                    ISBLANK ( 'Table'[Received Date] )
                )
    )
RETURN
    IF ( SUMX ( a, [nulls] ) > 0, 0, 1 )

[v-tangjie-msft · Accepted solution]

Hi @kaustubhgh ,

Thanks lbendlin  for the quick reply and solution. I have a other thought to add:

(1) We can create a new table.

Table 2 = 
var _table=SELECTCOLUMNS(FILTER('Table',[Quantity]>[Received Quantity]),"record",[Record Number])
RETURN FILTER('Table',not [Record Number] in _table)

(2) Or we can create a [Flag] measure and place [Flag=1] on the visual filter.

Flag = 
var _table=SELECTCOLUMNS(FILTER(ALL('Table'),[Quantity]>[Received Quantity]),"record",[Record Number])
RETURN IF(MAX('Table'[Record Number]) in _table,0,1)

(3)  Then the result is as follows.

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. 

[lbendlin · Accepted solution]

show =
VAR a =
    ADDCOLUMNS (
        VALUES ( 'Table'[Record Number] ),
        "nulls",
            VAR r = [Record Number]
            RETURN
                CALCULATE (
                    COUNTROWS ( ALLSELECTED ( 'Table' ) ),
                    'Table'[Record Number] = r,
                    ISBLANK ( 'Table'[Received Date] )
                )
    )
RETURN
    IF ( SUMX ( a, [nulls] ) > 0, 0, 1 )

[kaustubhgh]

Thank you for your reply.

I implemented the logic however I am getting 0 for all the records. Below is the screenshot for the reference. 

Kindly let me know if I am missing anything here.

Regards

[lbendlin]

looks like you created a calculated column. Delete it and create a measure instead.