Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

CalculateTable help

I need to return a calculate table with the datarows highlighted below. Basically All rows from the table where Marker =1  The column Marker is a DAX measure .  Report Date   EDW CustomerID...
Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
5 years ago

Anonymous 

This should work

New Table = CALCULATETABLE ( Table , Table[Marker] = 1 )


or this

New Table = FILTER ( Table , Table[Marker] = 1 )
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks for your Reply Fowmy .

 

But the CALCULATETBALE expression the way you mentioned above gives me an error 

Not sure why ? and how to get rid of this error?
Is this becasue IndexRow Measure is a DAX measure with zeros/ones value? 
Please help me understand this error as well the best possible approach to achieve the desired output
Thanks 

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    5 years ago

    HI Anonymous ,

     

    You have mentioned you need to calculatetable where marker = 1 right? So your code would be like below:-

    member_channel_new = 
CALCULATETABLE (
    'Member channel Utilization',
    'Member channel Utilization'[marker] = 1
)

    Thanks,

    Samarth

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    Anonymous 

    First, you need to be clear about creating tables and measures. When you create a table to add to your model, you are not supposed to use measures as measures are calculated once you complete adding the tables.  Measures work on the filter context present in your report environment. 

    Let me know the calculation for your [Marker] measure, then you can create a new table by adding this measure in your new table.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks for the reply Fowmy . 
      To give you full context of the scenario...the table above has two DAX  measures , Index Row Measure and Marker ..why I need to keep it that way is becasue my data changes based on the date slicer ....So as you can imagine the IndexRow Measure value will change if the date range selected by the user is different from the one shown above in the table . 

      Calculations for the both the DAX Measures is as shown below ::::

      Marker =
      VAR intIndex = [Index]
      VAR intCustomerID = SELECTEDVALUE('Member Channel Utilization'[EDWCustomerID])
      VAR dteReport = SELECTEDVALUE('Member Channel Utilization'[Report Date])
      VAR dteMax = CALCULATE(
      MAX('Member Channel Utilization'[Report Date])
      , ALLSELECTED('Member Channel Utilization')
      , 'Member Channel Utilization'[EDWCustomerID] = intCustomerID
      )
      VAR boolMatch = IF(dteReport = dteMax, 1, 0)
      RETURN
      boolMatch
       
       
      IndexRow Measure = CALCULATE(
      IF ( ISBLANK ( COUNTROWS ('Member Channel Utilization' ) ), 0, COUNTROWS ('Member Channel Utilization') ),
      FILTER(
      ALLSELECTED( 'Member Channel Utilization' ),
      ('Member Channel Utilization'[Report Date]) <= MIN('Member Channel Utilization'[Report Date]) && ('Member Channel Utilization'[EDWCustomerID]) = MIN( 'Member Channel Utilization'[EDWCustomerID] )
      )
      )
      Taking into consideration that i have these two measures on my table ....
      Is it possible to build a calculate table and/or a temp table which captures the records that are set to 1.
      Please clarify /help understand....

      Thanks