Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

drived value based on a dataset

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 NumberItemQuantityReceived DateReceived QuantityComments
1001Pen2 0Hide
1001Cooker310/15/20243Hide
1001Ipad110/13/20241Hide
1002Lamp310/16/20243Show
1002Bulb210/16/20242Show
1003Ipad110/17/20241Show

 

I have this dataset coming from the Import Query table.

Requesting your help to implement this logic.

Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    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 )
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

     

    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. 

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    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 )
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        lbendlin
        Icon for Super User rankSuper User
        1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

     

    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.