How do i convert the tableau LOD Calculation to Power BI. I am not getting the desired output.

[Ritika0307]

I have a LOD calculation in tableau as below.

{FIXED [Casenumber],[Adobe Date],[Account_Name__C],[Contact_Name__C],[Case Created Date],[Fiscal Date],[SEARCH_TERM],[Asset Name]

: COUNTD(IF ([Scenario (trim)] = 'Web activities but No Case Created')

 THEN [Casenumber] END)

}

It returns a distinct count of casenumbers based on the columns mentioned in the fixed syntax.

I tried converting the same in Power bi using the below DAX. But i am not getting correct output.

Self_service_success = CALCULATE(DISTINCTCOUNT('TB'[CASENUMBER]),'TB'[SCENARIO]="Web activities but No Case Created",ALLEXCEPT('TB','TB'[CASENUMBER],'TB'[V05_DATE],'TB'[ACCOUNT_NAME__C],'TB'[CONTACT_NAME__C],'TB'[CREATEDDATE],'TB'[FISCAL_MONTH],'TB'[V02_SEARCH_TERM],'TB'[V20_ASSET_NAME]))

Is there any other way in which i can convert the LOD to power bi DAX measure?

[Anonymous]

Hi @Ritika0307 

CALCULATE(
DISTINCTCOUNT(
IF(
[Scenario (trim)] = "Web activities but No Case Created",
[Casenumber]
)
),
FILTER(
ALL(
'Table'[Casenumber],
'Table'[Adobe Date],
'Table'[Account_Name__C],
'Table'[Contact_Name__C],
'Table'[Case Created Date],
'Table'[Fiscal Date],
'Table'[SEARCH_TERM],
'Table'[Asset Name]
),
'Table'[Casenumber] = MAX('Table'[Casenumber]) &&
'Table'[Adobe Date] = MAX('Table'[Adobe Date]) &&
'Table'[Account_Name__C] = MAX('Table'[Account_Name__C]) &&
'Table'[Contact_Name__C] = MAX('Table'[Contact_Name__C]) &&
'Table'[Case Created Date] = MAX('Table'[Case Created Date]) &&
'Table'[Fiscal Date] = MAX('Table'[Fiscal Date]) &&
'Table'[SEARCH_TERM] = MAX('Table'[SEARCH_TERM]) &&
'Table'[Asset Name] = MAX('Table'[Asset Name])
))

Best Regards,

Shreya

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[OwenAuger]

Hi @Ritika0307 

To replicate the behaviour of this FIXED LOD calculation, I would suggest using REMOVEFILTERS/SUMMARIZE rather than ALLEXCEPT.

I believe this should work as expected:

Self_service_success =
CALCULATE (
    DISTINCTCOUNT ( 'TB'[CASENUMBER] ),
    'TB'[SCENARIO] = "Web activities but No Case Created",
    -- Remove filters on TB table...
    REMOVEFILTERS ( 'TB' ),
    -- ...but add back a filter corresponding to visible combinations of these columns
    SUMMARIZE (
        'TB',
        'TB'[CASENUMBER],
        'TB'[V05_DATE],
        'TB'[ACCOUNT_NAME__C],
        'TB'[CONTACT_NAME__C],
        'TB'[CREATEDDATE],
        'TB'[FISCAL_MONTH],
        'TB'[V02_SEARCH_TERM],
        'TB'[V20_ASSET_NAME]
    )
)

The reason not to use ALLEXCEPT in this case is that it will only retain filters that exist on the specific columns listed, but will not retain values in those columns resulting in crossfiltering from other columns. In Tableau, FIXED does retain filters resulting from crossfiltering on the listed columns.

Does the above expression work as expected?

Regards

[Ritika0307]

Hello , i tried the above calculation , seems like after summarizing the columns it is only returning the distinct count of the casenumber. I want to see the count of the distinct rows the summarize statement is returning. ( I believe summarize is similar to group by).

If possible , i want to get DAX calculation equivalent to this sql statement.

SELECT sum(count(DISTINCT(CASENUMBER))) FROM TB WHERE SCENARIO ='Web activities but No Case Created'  GROUP BY V05_DATE ,ACCOUNT_NAME__C ,CONTACT_NAME__C ,CREATEDDATE ,FISCAL_MONTH ,V02_SEARCH_TERM ,V20_ASSET_NAME ; 

Thanks