Could not able to filter the slicer value for my dax

[mallik999]

Hi All,

I have a scanario ,want to get the % information based on daily,inorder to display based on slicer selecction as well. 

the below first image i am getting the proper output when i haven't select my slicer,when i select my slicer the values(measure) is not filtering.

here Measure is : Daily wise SensorOccupancy

%_info is : sensoroccupancy/measure*100

when i select slicer the value(measure) also should give the value based on selection and Day wise.

could you please suggest how to get the values based on slicer selection,i am using query in second image.

Regards

Mallikarjun

[mallik999 · Accepted solution]

Hi @Anonymous  Thanks for your reply.

its not working,giving same results

any idea how to use allexcept and allselected in the same Query,it may get correct ouput i feel.

[Greg_Deckler]

@mallik999 - No idea what is going on here. Check to make sure you have proper relationships between tables. 

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[mallik999]

@Greg_Deckler Thanks For your reply,

here i am getting from single table only and also attched screen print for desired output.

in Measure it showing the values for (6,3)Day wise ,but i want change the values based on slicer selection also.

you can see the below screen print which i writen dax formula.

Regards

Mallikarjun

[Anonymous]

HI @mallik999 

In the ALLEXCEPT section of the below measure add Sample[Room id]

So, your measure should say CALCULATE(SUM(Sample[SensorOccupancy]), ALLEXCEPT(Sample,Sample[LocalDate],Sample[Room id]))

or

CALCULATE(SUM(Sample[SensorOccupancy]), ALLEXCEPT(Sample,Sample[Room id]))

[mallik999]

@Anonymous Thanks for solution,

I have taken the room_id from the same table and included in allexcept as you said,it worked for me,

Thanks for your time,accepted your solution.

Regards

Mallikarjun

[Anonymous · Accepted solution]

Hi @mallik999 

Please accept my reply as a solution. you have accepted your own reply as a solution which will misguide if anyone wants to refer to it.

[mallik999 · Accepted solution]

Hi @Anonymous  Thanks for your reply.

its not working,giving same results

any idea how to use allexcept and allselected in the same Query,it may get correct ouput i feel.

[Anonymous]

Hi @mallik999 

Can you share your sample file or sample dataset, I need to understand how your RoomID column is arranged in ration to other data.

[mallik999]

@Anonymous  please find the below link for sample file.

sample File 

Regards

Mallikarjun

[Anonymous]

HI @mallik999 

Check the below formula to get the % column

pcnt_info = 
VAR x =
ADDCOLUMNS(
    SUMMARIZE(
        FILTER(
            ALLSELECTED('Samples') ,
             'Samples'[LocalDate]=MAX('Samples'[LocalDate]) 
            ) ,
        'Samples'[LocalDate] ,
        'Samples'[LocalTIme] ,
        "Available" , 
            CALCULATE(
                COUNTROWS('Samples'),
                FILTER(
                    ALLSELECTED('Samples'), 
                    'Samples'[LocalDate]=MAX('Samples'[LocalDate]) && 
                    'Samples'[LocalTIme] = MAX('Samples'[LocalTIme]) 
                    ) 
                ),
        "Occupied" , 
                    CALCULATE(
                COUNTROWS('Samples'),
                FILTER(
                    ALLSELECTED('Samples'), 
                    'Samples'[LocalDate]=MAX('Samples'[LocalDate]) && 
                    'Samples'[LocalTIme] = MAX('Samples'[LocalTIme]) && 'Samples'[SensorOccupancy] = 1
                    ) 
                )
            ),"pcnt_info" , DIVIDE([Occupied],[Available],0))
RETURN MAXX(x,[pcnt_info])