Convert a Tableau Calculation into Power BI Dax -

[KabirG]

Hi,

I am a new to Power BI and I want to convert this calculation in Tableau to Power BI using DAX.  Appreciate if someone can explain what this means and how can this be done.

IF First()=0 THEN WINDOW_SUM(COUNTD(ID)) END

Thank you! 🙂

[Kedar_Pande]

Measure = 
IF (
RANKX(ALLSELECTED(Table), [ID],, ASC, Dense) = 1,
CALCULATE(DISTINCTCOUNT(Table[ID]), ALLSELECTED(Table))
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[123abc · Accepted solution]

Tableau Calculation:

IF First()=0 THEN WINDOW_SUM(COUNTD(ID)) END

1. First(): This function in Tableau returns the index of the current row relative to the first row in the partition. So, First()=0 is used to check if the current row is the first one in the partition.
2. COUNTD(ID): This counts the distinct values of ID.
3. WINDOW_SUM(COUNTD(ID)): This sums the distinct count of ID over the entire window (i.e., partition).
4. IF First()=0 THEN ... END: This ensures that the calculation is performed only for the first row in the window and returns null for other rows.

5. Equivalent DAX Translation:

   Power BI does not have a direct WINDOW_SUM function, but we can achieve similar functionality using a combination of CALCULATE, DISTINCTCOUNT, and FILTER.

   Here's a step-by-step approach to translating it into DAX:

   Please try this below:

   DAX

   IF( ISFILTERED([ID]) || RANKX(ALLSELECTED(TableName), TableName[ID], ,ASC) = 1, CALCULATE(DISTINCTCOUNT(TableName[ID])), BLANK() )

    

   Formila Breakup:

   • RANKX: This ranks the rows based on ID. We use RANKX to simulate First()=0 by checking if the rank of the current row is 1.
   • CALCULATE(DISTINCTCOUNT(TableName[ID])): This counts the distinct ID values.
   • BLANK(): If the current row is not the first one (rank is not 1), it returns a blank.

     This DAX formula will return the distinct count of ID only for the first row and will show blank for other rows, similar to how the Tableau calculation works.