Qlik logic to PBi

[Bharathi09]

Hello All, please help me in converting below Qlik logic to PBI if someone knows PBI

=count
(
{
<
[Date]={">=$(=AddYears( date(max({< [ State]={'B'}>}[ Date] )),-1))<=$(=AddMonths( date(max({< [ State]={'B'}>}[Date] )),-1))"},
TYPE={'PTS'},
[ State]={'B'}
>
}
DISTINCT [EN Number]
)

[Sayed_Mannan]

Sure, I can help with that. The Qlik expression you provided is counting the distinct "EN Number" where "Type" is 'PTS', "State" is 'B', and "Date" is between the maximum date of "State" 'B' - 1  year and the maximum date of "State" 'B' - 1 month.

In Power BI, you can achieve the same result using a combination of CALCULATE, COUNTROWS, FILTER, MAX, EDATE, and DISTINCT functions. Here's how you can do it:

CALCULATE (
COUNTROWS ( DISTINCT ( YourTable[EN Number] ) ),
FILTER (
YourTable,
YourTable[TYPE] = "PTS" &&
YourTable[State] = "B" &&
YourTable[Date] >= EDATE ( MAX ( YourTable[Date] ), -12 ) &&
YourTable[Date] <= EDATE ( MAX ( YourTable[Date] ), -1 )
)
)

Please replace YourTable with your actual table name.

This DAX formula will give you the count of distinct "EN Number" under the same conditions as your Qlik expression.

Let me know if this helps

[TauseefKhan]

Hi @Bharathi09,

1- Calculate the maximum date first where State = 'B'
2- Add variables to determine the start and end date of the desired range.
3- Implement the date range and other filters i.e. TYPE and State to count distinct [EN Number]

Measure = 
VAR Max_Date = 
    CALCULATE(
        MAX('Table'[Date]),
        'Table'[State] = "B"
    )

VAR Start_Date = EDATE(Max_Date, -12)
VAR End_Date = EDATE(Max_Date, -1)

RETURN
    CALCULATE(
        DISTINCTCOUNT('Table'[EN Number]),
        'Table'[Date] >= Start_Date &&
        'Table'[Date] <= End_Date &&
        'Table'[TYPE] = "PTS" &&
        'Table'[State] = "B"
    )

 ** When applicable please mark the correct/appropriate replies as "solution". Please LIKE threads if the provided solution is helpful to. **

[Bharathi09]

MM =

VAR Max_Date =

    CALCULATE(

        MAX('DATA'[Date]),

        'FACT (2)'[State] = "B"

    )

VAR Start_Date = EDATE(Max_Date, -12)

VAR End_Date = EDATE(Max_Date, -1)

RETURN

    CALCULATE(

        DISTINCTCOUNT('FACT (2)'[En Number]),

        'DATA'[Date] >= Start_Date &&

        'DATA'[Date] <= End_Date &&

        'DATA'[TYPE] = "PTS" &&

        'FACT (2)'[State] = "B"

    )
The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression.

Getting above error
Please help