topic Re: Get filtered value in graphic or card in DAX Commands and Tips

Get filtered value in graphic or card

shuijgen — Wed, 15 Feb 2023 15:48:08 GMT

I have a filter of customers on top of my page. In this filter one may select 1 or a few or all customers with a certain %.

This % for that specific customer should be calculated as a new value.

This goes well in the Table I have on the page. Every table line per customer is calculated correct if a selection is made or not in that filter. But..

in the Graphic and Tile I would like to add I want to see the total of all customers. So the recalculated number if a filter selection is made. I have the measure as shown below. In the part _selection it goes wrong.

MIN(D_Klantnaam(forecast).... results in a recalculated value if the first customer is selected in the value, but none of the other selected customers are taken into account in the recalculated value.

If I change the measure in MAX(D_Klantnaam(forecast)... results in a recalculated value if the last customer is selected in the value, but none of the other selected customers are taken into account in the recalculated value.

The VAR _klant = SELECTEDVALUE(.....) is not correct I suppose. If multiple selections are made there is more than one selected value. How do I change this measure so all selected customers in the filter are taken into account ?

Re: Get filtered value in graphic or card

lbendlin — Tue, 21 Feb 2023 03:00:44 GMT

Learn about aggregator functions like SUMMARIZE, SUMX etc and about intermediate table variables. Think like the Grand Total. Very often measures designed for the Grand Total will also work for the Row and Column Totals and for the individual cells.

Re: Get filtered value in graphic or card

Anonymous — Tue, 21 Feb 2023 06:14:13 GMT

Hi shuijgen ,

Please update the formula of measure [Klanten forecast ] as below and check if it can return the expected result...

Klanten forecast =
VAR _klant =
    SELECTEDVALUE ( 'Forecast-multiplier'[Klantnaam] )
VAR _multiplier =
    SELECTEDVALUE ( 'Forecast-multiplier'[Multiplier] )
VAR _minklant =
    CALCULATE ( MIN ( D_KLANT[Klantnaan(Forecast)] ), ALLSELECTED ( D_KLANT ) )
VAR _selection =
    SWITCH (
        TRUE (),
        _klant = BLANK ()
            || _multiplier = BLANK (), 0,
        _minklant = _klant, _multiplier
    )
VAR _uren =
    SUM ( F_FORECAST[Uren per dag] )
VAR _orderUren = [Artikelen uren nodig]
VAR _result =
    ( _uren + _orderUren )
        + DIVIDE ( _uren + _orderUren, 100 ) * _selection
RETURN
    _result

If the above one can't help you get the expected result, could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Re: Get filtered value in graphic or card

Anonymous — Fri, 24 Feb 2023 02:40:29 GMT

Hi shuijgen ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

Re: Get filtered value in graphic or card

shuijgen — Fri, 24 Feb 2023 06:51:59 GMT

Thanks all for the tips, but did not solve the case unfortunately.

there is a relation to an Excel file for some customers with a number of hours per production machine and some PowerBi tables and measures connected with ERP to calculate per customer the WIP and Forecast.

The dummy hours is this from the former post.

VAR _uren = SUM ( F_FORECAST[Uren per dag] )

Forecast_Dummy_Multiplier = // multiplier keuze(s) * bestaande dummy uren VAR _Klant = ALLSELECTED('Forecast-multiplier'[Klantnaam]) RETURN IF(ISFILTERED('Forecast-multiplier'[Multiplier %]), // berekening van de multiplier CALCULATE( VALUES('Forecast-multiplier'[Multiplier])/100 * SUM(F_FORECAST[Uren per dag]), FILTER('F_FORECAST',F_FORECAST[Klant] in _Klant)) + CALCULATE(SUM(F_FORECAST[Uren per dag]), FILTER('F_FORECAST',F_FORECAST[Klant] in _Klant)), 0)

I now have managed to have a correct measure to calculate the dummy hours for a multiple hierarchy slicer selection.

The table already did the job put the Card did not work properly (only for one selection). With the following code it now work for multiple selections:

TotaleUren_met_OHW = //met OHW IF([Hierarchy selected %]="", [Klanten bezetting] + [Klanten forecast] + [Forecast_Dummy_Multiplier], [Klanten bezetting] + [Forecast_Dummy_Multiplier])

To get it done including the measures from the PowerBi tables is the next step. To make a simple PBIX for understanding here for you guys is not easy 😞 see the complexity below).

This is for this from the former post: VAR _orderUren = [Artikelen uren nodig]

This [artikelen nodig] is a multiple thread, see:

Artikelen uren nodig = //ROUNDUP( SUMX( F_ORDERS, [Artikelen standaard uren nodig] * [Artikelen series nodig] )

Artikelen standaard uren nodig = SUMX( F_ORDERS, (F_ORDERS[Seriegrootte] * F_ORDERS[Cyclustijd]) + F_ORDERS[Insteltijd] ) / 60

Artikelen series nodig = VAR _levTal = CALCULATE( CALCULATE( [Order aantal], ALL( F_ORDERS ), VALUES( F_ORDERS[Ordernummer] ), VALUES( D_ARTIKEL[Omschrijving] ) ), REMOVEFILTERS( D_DATE ) ) VAR _seriesNodig = ROUNDUP( DIVIDE( _levTal, [Artikelen seriegrootte] ), 0 ) RETURN _seriesNodig

Order aantal = VAR _orderAantal = CALCULATE( CALCULATE( SUMX( DISTINCT(F_ORDERS[Ordernummer]), FIRSTNONBLANK(F_ORDERS[Leveraantal], 0) ) ), REMOVEFILTERS( D_DATE ) ) RETURN IF ( [Order startdatum] <> BLANK(), _orderAantal )

Artikelen seriegrootte = VAR _serieGrootte = CALCULATE( CALCULATE( MIN( F_ORDERS[Seriegrootte] ), VALUES( F_ORDERS[Ordernummer] ), VALUES(F_ORDERS[Artikelnummer] ) ), REMOVEFILTERS( D_DATE ) ) RETURN IF( HASONEFILTER( D_ARTIKEL[Artikelnummer] ) && [Order startdatum] <> BLANK(), _serieGrootte )

Order startdatum = MIN( F_ORDERS[Startdatum] )

Re: Get filtered value in graphic or card

shuijgen — Fri, 24 Feb 2023 09:05:04 GMT

appologies.. unfortunately above mentioned is not working.

Only when I have the same percentage for the multiple customers