How can I filter a column by another column?

[akay23]

Hi,

I am rookie on PBI.

I have a measure that first I want the find the all column values [Arr_Airport_Code] in a table that has blank value [frequency] in two months earlier. And than I want to this filtered column's all frequencies. But I can not show the whole year frequency of the values that just two months earlier's frequencys are blank.

Here is my measure. Thanks

VAR t1 =

SUMMARIZE (

OAG_Deneme_Veri,

OAG_Deneme_Veri[Dep Airport Code],

OAG_Deneme_Veri[Arr_Airport_Code],

OAG_Deneme_Veri[Time series],

OAG_Deneme_Veri[Carrier Code],

"frekans", SUM ( OAG_Deneme_Veri[Frequency] )

)

VAR t2 =

ADDCOLUMNS (

t1,

"önceki ay frekans",

CALCULATE (

SUM ( OAG_Deneme_Veri[Frequency] ),

DATEADD (OAG_Deneme_Veri[Time series] , -1, MONTH )

))

VAR t3 =

ADDCOLUMNS (

t2,

"2 önceki ay frekans",

CALCULATE (

SUM ( OAG_Deneme_Veri[Frequency] ),

DATEADD (OAG_Deneme_Veri[Time series] , -2, MONTH ) ))

VAR t4 =

ADDCOLUMNS (

t3,

"ddd",

IF ( [önceki ay frekans] = BLANK() && [2 önceki ay frekans] = BLANK(), "Var", "Yok" )

)

VAR t5 =

SUMMARIZE(

FILTER ( t4, [ddd] = "Var" ),

OAG_Deneme_Veri[Arr_Airport_Code]

)

Var t6 =

    SUMMARIZE(

    FILTER(OAG_Deneme_Veri, [Arr_Airport_Code] = MINX(t5,[Arr_Airport_Code])),

    OAG_Deneme_Veri[Dep Airport Code],

    OAG_Deneme_Veri[Time series],

"aaa", values(OAG_Deneme_Veri[Arr_Airport_Code]),

    "frr" , sum(OAG_Deneme_Veri[Frequency])

)

Return

calculate(SUMX(t6,[frr]),ALL())

[VahidDM]

Hi @akay23 

Can you post sample data as text and expected output?
Not enough information to go on;

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.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

[akay23]

Carrier code	Dep Airport Code	Arr Airport Code	Frequency	Time Series
AB	ABC	XYZ	0	1.01.2018
AB	ABC	XYZ	0	1.02.2018
AB	ABC	XYZ	0	1.03.2018
AB	ABC	XYZ	0	1.04.2018
AB	ABC	XYZ	0	1.05.2018
AB	ABC	XYZ	0	1.06.2018
AB	ABC	XYZ	0	1.07.2018
AB	ABC	XYZ	0	1.08.2018
AB	ABC	XYZ	13	1.09.2018
AB	ABC	XYZ	14	1.10.2018
AB	ABC	XYZ	15	1.11.2018
AB	ABC	XYZ	16	1.12.2018
AB	ABC	DFG	16	1.01.2018
AB	ABC	DFG	16	1.02.2018
AB	ABC	DFG	16	1.03.2018
AB	ABC	DFG	16	1.04.2018
AB	ABC	DFG	16	1.05.2018
AB	ABC	DFG	16	1.06.2018
AB	ABC	DFG	16	1.07.2018
AB	ABC	DFG	16	1.08.2018
AB	ABC	DFG	0	1.09.2018
AB	ABC	DFG	0	1.10.2018
AB	ABC	DFG	16	1.11.2018
AB	ABC	DFG	16	1.12.2018

This is my raw data example. When I select 01/08/2018 on time slicer, I want to data below.

Carrier code	Dep Airport Code	Arr Airport Code	Frequency	Time Series
AB	ABC	XYZ	5	1.01.2018
AB	ABC	XYZ	6	1.02.2018
AB	ABC	XYZ	7	1.03.2018
AB	ABC	XYZ	8	1.04.2018
AB	ABC	XYZ	9	1.05.2018
AB	ABC	XYZ	10	1.06.2018
AB	ABC	XYZ	0	1.07.2018
AB	ABC	XYZ	0	1.08.2018
AB	ABC	XYZ	13	1.09.2018
AB	ABC	XYZ	14	1.10.2018
AB	ABC	XYZ	15	1.11.2018
AB	ABC	XYZ	16	1.12.2018

When I select a month on date slicer in my visual. Which [Dep Airport Code] is open and what is these airport codes sum frequency by month.

I hope this example make you clear thank you 

[VahidDM]

@akay23 

How can find which [Dep Airport Code] is open? and how did you calculate the Frequency numbers in the result table:

Can you add more details?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[akay23]

When I select a month. First, I look to frequency column. If last 2 month's frequency is zero, I accept as this month is opening month. In this example when I select 01.09.2018, check 01.08.2018 and 01.07.2018 and both of is zero for ABC [Dep Airport Code]. So opening month of ABC is 01.08.2018.

Now I can find all Dep Airport Code that opening date is on 01.08.2018. Then I want to show whole months of these Dep Airport Code.

In my first post I tried to calculate the opening Dep Airport Code on selected date. Then tried to filter to all data just these Dep Airport Code. But It did not work properly. Just show me opening month frequency of Dep Airport Code, not whole year.

It maybe make clear for you. Thank you

[akay23]

And I want to left table with whole year frequency. When I select from date slicer 01.08.2021 - 01.12.2021, Just I want to show whole frequncy by month but just opening date is on 01.08.2021 - 01.12.2021.

[akay23]

opening_deneme3 = 
 	VAR t1 =
        SUMMARIZE (
            OAG_Deneme_Veri,
            OAG_Deneme_Veri[Dep Airport Code],
            OAG_Deneme_Veri[Arr_Airport_Code],
            OAG_Deneme_Veri[Time series],
            OAG_Deneme_Veri[Carrier Code],
 
            "frequnecy", SUM ( OAG_Deneme_Veri[Frequency] )
        )
    VAR t2 =
        ADDCOLUMNS (
            t1,
            "last month frequency",
                CALCULATE (
                    SUM ( OAG_Deneme_Veri[Frequency] ),
                    DATEADD (OAG_Deneme_Veri[Time series] , -1, MONTH ) 
        ))
    VAR t3 =
        ADDCOLUMNS (
            t2,
            "2 months earlier frquency",
                CALCULATE (
                    SUM ( OAG_Deneme_Veri[Frequency] ),
                    DATEADD (OAG_Deneme_Veri[Time series] , -2, MONTH ) ))
    VAR t4 =
        ADDCOLUMNS (
            t3,
            "ddd",
                IF ( [last month frequency] = BLANK() && [2 months earlier frquency] = BLANK(), "IN", "OUT" )
        )
    VAR t5 =
        SUMMARIZE( 
            FILTER ( t4, [ddd] = "IN" ),
            OAG_Deneme_Veri[Arr_Airport_Code]

)

    Var t6 = 
    	SUMMARIZE(
    	FILTER(OAG_Deneme_Veri, [Arr_Airport_Code] = MINX(t5,[Arr_Airport_Code])),
    	
    	OAG_Deneme_Veri[Dep Airport Code],
    	OAG_Deneme_Veri[Time series],
        "aaa", values(OAG_Deneme_Veri[Arr_Airport_Code]),
    	"frr" , sum(OAG_Deneme_Veri[Frequency])
    	
)
Return
calculate(SUMX(t6,[frr]),ALL())

This is my measure for this. But I cannot calculate like what I want

[v-chenwuz-msft]

Hi @akay23 ,

I have encountered this problem. Possibly, it is the slicer cutting the table as data input to measure open_deneme3. I notice that you added ALL(), however, this does not work. In fact, you should add it to t1.

Like the following code:

 	VAR t1 =
        SUMMARIZE (
            ALL( OAG_Deneme_Veri),
            OAG_Deneme_Veri[Dep Airport Code],
            OAG_Deneme_Veri[Arr_Airport_Code],
            OAG_Deneme_Veri[Time series],
            OAG_Deneme_Veri[Carrier Code],
 
            "frequnecy", SUM ( OAG_Deneme_Veri[Frequency] )

or  ALLEXCEPT() if you want keep some filters on the table input to the measure.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[akay23]

hi @v-chenwuz-msft ,

Thank you for your reply, 

I used your code like,

VAR orig =
        SELECTEDVALUE ( OAG_Deneme_Veri[Dep Airport Code] )
    VAR cc =
        SELECTEDVALUE ( OAG_Deneme_Veri[Carrier Code] )

    
    VAR t1 =
        SUMMARIZE (
            FILTER (
                ALL ( OAG_Deneme_Veri ),
                [Dep Airport Code] = orig
                    && [carrier code] = cc
                    
            ),
            OAG_Deneme_Veri[Dep Airport Code],
            OAG_Deneme_Veri[Arr Airport Code],
            OAG_Deneme_Veri[Time series],
            OAG_Deneme_Veri[Carrier Code],
            "frequnecy", SUM ( OAG_Deneme_Veri[Frequency] )
        )
    VAR t2 =
        ADDCOLUMNS (
            t1,
            "last month frequency",
                CALCULATE (
                    SUM ( OAG_Deneme_Veri[Frequency] ),
                    DATEADD ( OAG_Deneme_Veri[Time series], -1, MONTH )
                )
        )
    VAR t3 =
        ADDCOLUMNS (
            t2,
            "2 months earlier frquency",
                CALCULATE (
                    SUM ( OAG_Deneme_Veri[Frequency] ),
                    DATEADD ( OAG_Deneme_Veri[Time series], -2, MONTH )
                )
        )
    VAR t4 =
        ADDCOLUMNS (
            t3,
            "ddd",
                IF (
                    [last month frequency] = BLANK ()
                        && [2 months earlier frquency] = BLANK (),
                    "IN",
                    "OUT"
                )
        )
    VAR t5 =
        SUMMARIZE ( FILTER ( t4, [ddd] = "IN" ), OAG_Deneme_Veri[Arr Airport Code] )
    VAR t6 =
        SUMMARIZE (
            FILTER (
                All(OAG_Deneme_Veri),
                [Arr Airport Code] = MINX ( t5, [Arr Airport Code] )
                    && [dep Airport Code] = orig1
                    && [carrier code] = cc1
            ),
            OAG_Deneme_Veri[Dep Airport Code],
            OAG_Deneme_Veri[Arr Airport Code],
            OAG_Deneme_Veri[Time series],
            "frr", SUM ( OAG_Deneme_Veri[Frequency] )
        )
return
sumx(t6,[frr])

But first ALL () I used for it, I want to use a filter to accelarate my calculation like [Dep Airport Code] = orig && [ carrier code] = cc. But when I use a filter like this [Time Series] column are also filtered. How can I solve this issue? Thank you 

[v-chenwuz-msft · Accepted solution]

Hi @akay23 ,

Maybe you can try REMOVEFILTER('table'[Time Series]). I used to recommend that you use allexcept() to keep the fields you want to keep the filter, all other filters will be removed. You can try it.

Or you can try creating a new table via values('table'[Time Series]) and there no relationship between these tables. Add this field to a slicer,  Filter OAG_Deneme_Veri or other tables by passing values as string from the new table. This can be cumbersome, but it allows you to filter a field individually without affecting other fields that you don't need to filter.

Best Regards

Community Support Team _ chenwu zhu

[akay23]

Hi @v-chenwuz-msft ,

Thank you for your support.

I use new table after your suggestion. And it works. 

If I dont use a new table it doesnt work but now iti is perfect. thanks again

    VAR selectedmonthtarihstart= FIRSTDATE(ALLSELECTED(TarihOpening[Date]))
    VAR selectedmonthtarihfinish= LASTDATE(ALLSELECTED(TarihOpening[Date]))
    
    VAR selecteddep =
        SELECTEDVALUE ( OAG_Deneme_Veri[Dep Airport Code] ) 
    VAR selectedcar =
        SELECTEDVALUE ( OAG_Deneme_Veri[Carrier Code] )

    VAR t1 =
        CALCULATETABLE (
            OAG_Deneme_Veri,
            OAG_Deneme_Veri[Dep Airport Code] = selecteddep1,
            DATESBETWEEN(OAG_Deneme_Veri[Time series],selectedmonthtarihstart,selectedmonthtarihfinish),
            OAG_Deneme_Veri[Carrier Code] = selectedcar1
        )
    VAR t2 =
        SUMMARIZE (
            t1,
            OAG_Deneme_Veri[Carrier Code],
            OAG_Deneme_Veri[Dep Airport Code],
            OAG_Deneme_Veri[Arr_Airport_Code],
            OAG_Deneme_Veri[Time series],
            "freq", SUM ( OAG_Deneme_Veri[Frequency] )
        )
    VAR t3 =
        ADDCOLUMNS (
            t2,
            "önceki ay frekans",
                CALCULATE (
                    SUM ( OAG_Deneme_Veri[Frequency] ),
                    DATEADD ( OAG_Deneme_Veri[Time series], -1, MONTH )
                )
        )
    VAR t4 =
        ADDCOLUMNS (
            t3,
            "2 önceki ay frekans",
                CALCULATE (
                    SUM ( OAG_Deneme_Veri[Frequency] ),
                    DATEADD ( OAG_Deneme_Veri[Time series], -2, MONTH )
                )
        )
    VAR t5 =
        ADDCOLUMNS (
            t4,
            "kontrol",
                IF (
                    [önceki ay frekans] = BLANK ()
                        && [2 önceki ay frekans] = BLANK (),
                    "Var",
                    "Yok"
                )
        )
    VAR t6 =
        SUMMARIZE ( FILTER ( t5, [kontrol] = "Var" ), OAG_Deneme_Veri[Arr_Airport_Code] )

    VAR t7 =
        CALCULATETABLE(OAG_Deneme_Veri,
               
                OAG_Deneme_Veri[Arr_Airport_Code] = MINX ( t6, [Arr_Airport_Code] ))
                    
    VAR t8 = SUMMARIZE (
            t7,
            OAG_Deneme_Veri[Carrier Code],
            OAG_Deneme_Veri[Dep Airport Code],
            OAG_Deneme_Veri[Arr_Airport_Code],
            OAG_Deneme_Veri[Time series],
            "freq1", SUM ( OAG_Deneme_Veri[Frequency] )
        ) 

Return
Minx(t8,[freq1])