DAX Help

[fabconmvp]

Hello everyone,

I need help about dax. First of all ı have a table visualization in PBI. And below the image is example of how PBI visualization. I have 2 table. 1 Fact table and other DimDate table. There is a 1(dimdate) to many(fact) based on Date. Weeks and Date comes from DimDate table. Value is a measure which is SUM(Fact[Value]). and I want to create a measure which gives max value for SUM(Fact[Value]) based on weeks and lastly ı have some slicers that needs to effected. So whenever slicer changes max value needs to be effected.

Fact Table:

The table I visualized in PBI:

Thanks for supports.

[Ashish_Mathur]

Hi,

Try this measure

=MAXX(ALLSELECTED(Calendar[weeks]),[Value])

Hope this helps.

[fabconmvp]

Hello,

İt doesn't work. The values doesn't change. Thank you for support.

[Ashish_Mathur]

You are welcome.  Share the download link of the PBI file.

[techies · Accepted solution]

Hi @fabconmvp 

Please check this

Max_Weekly_Value_Per_Week =

VAR CurrentWeek = SELECTEDVALUE('date'[Week])

VAR WeeklyValues =

    CALCULATETABLE(

        SUMMARIZE(

            Sheet2,

            'date'[Week],

            Sheet2[Value]

        ),

        ALLEXCEPT('date', 'date'[Week])

    )

RETURN

    MAXX(FILTER(WeeklyValues, 'date'[Week] = CurrentWeek), [Value])

[fabconmvp]

Hello,

Firstly, thank you for support. I guess some of the column missing but I modified it in your measure. I'm testing detailly right now that effected other slicers etc. I'm going to accept accept the solution if ı have no question. Thank you for support.
"

test =

VAR CurrentWeek = SELECTEDVALUE(DimDate[WeekNum])

VAR WeeklyValues =

    CALCULATETABLE(

        SUMMARIZE(

            'Fact',

            DimDate[Date],

            DimDate[WeekNum],

            "Value",

            SUM('Fact'[value])

        ),

        ALLEXCEPT(DimDate, DimDate[WeekNum])

    )

RETURN

    MAXX(FILTER(WeeklyValues, DimDate[WeekNum] = CurrentWeek),[Value])
"

[anmolmalviya05]

Hi @fabconmvp, Hope you are doing good,

Please try the below measure:

Max Value per Week =
MAXX(
VALUES(DimDate[Week]),
CALCULATE( SUM(Fact[Value]) )
)
If this post helps to answer your question, please consider accepting it as a solution so others can find it more quickly when they face a similar challenge.

Proud to be a Microsoft Fabric community super user

Let's Connect on LinkedIn

Subscribe to my YouTube channel for Microsoft Fabric and Power BI updates.

[fabconmvp]

Hello,

Thank you for support. However, it didnt work. The values shown as in the first image. 

[johnt75]

I think you can use

Max Value =
MAXX ( ALLSELECTED ( 'DimDate'[Week] ), [Value] )

[fabconmvp]

Hello,

Thank you for support. However, it didnt work. The values shown as in the first image. 

[johnt75]

Is your DimDate table marked as a date table ?

[fabconmvp]

Hello,

Yes marked as a date.