Scenario -1IDDate Name DirectionExpected Result 1Jul-04AgentOutboundConsider1Jul-04Customer AInbound 1Jul-04AgentOutboundConsider1Jul-04Customer AInbound In Scenario 1 , we need to consider One Inbound and One outbound Respectuly. Scenario-2IDDateNameDirectionExpected Result1Jul-04AgentOutboundconsider1Jul-04Customer AInbound 1Jul-04Customer AInbound 1Jul-04Customer AInbound 1Jul-04AgentOutboundNot considerIn Scenario 2, if we have Multiple Inbound We nee to consider only One bound for Respective One Outbound, other wise no need to consider outboundScenario-3IDDateNameDirectionExpected Result106-07-2004AgentOutboundConsider106-07-2004Customer AInbound 106-07-2004Customer AInbound 105-07-2004Customer AInbound 104-07-2004AgentOutbound Not consider104-07-2004AgentOutbound Not consider104-07-2004AgentOutbound Not consider104-07-2004Customer AOutboundConsider103-07-2004Customer AInbound 103-07-2004Customer AOutboundNot Consider IDDateName DirectionExpected Result104-07-2004AgentOutboundConsider103-07-2004Customer AInbound 102-07-2004Customer AInbound 101-07-2004Customer AInbound IDDateName direction Expected Result104-07-2004Customer AOutboundNot consider103-07-2004Customer AOutbound Not consider102-07-2004Customer AOutbound Not consider101-07-2004Customer AOutbound Not considerIf we need to consider outbound if we have a Inbound in below record , also if we have multiple Inbound and Multiple outbound,here we need to consider one outbound for respective one inbound. Could you Please check and share us the solution Thanks in Advance.

Hi karthickpbi , In scenario -1, please try following DAX: Expected Result = SWITCH( TRUE(), 'Scenario -1'[Name]="Agent","Consider" ) In scenario -2, Please create an index column from 1 to 5 first, then try following DAX: Expected Result = SWITCH( TRUE(), 'Scenario-2'[Name] = "Agent" && 'Scenario-2'[Index] = MIN('Scenario-2'[Index]),"Consider", 'Scenario-2'[Name] = "Agent" && 'Scenario-2'[Index] > MIN('Scenario-2'[Index]),"Not Consider" ) In scenario -3(1), please try following DAX: Expected Result = SWITCH( TRUE(), 'Scenario-3(1)'[Direction]="Inbound",BLANK(), 'Scenario-3(1)'[Name]= "Agent" && 'Scenario-3(1)'[Date]=MAX('Scenario-3(1)'[Date]),"Consider", 'Scenario-3(1)'[Name]= "Agent" && 'Scenario-3(1)'[Date]<MAX('Scenario-3(1)'[Date]),"Not Consider", 'Scenario-3(1)'[Name]= "Customer A" && 'Scenario-3(1)'[Date]= MIN('Scenario-3(1)'[Date]),"Not Consider", 'Scenario-3(1)'[Name]= "Customer A" && 'Scenario-3(1)'[Date]> MIN('Scenario-3(1)'[Date]),"Consider" ) In scenario -3(2), please try following DAX: Expected Result1 = SWITCH( TRUE(), 'Scenario-3(2)'[Name]= "Agent" && 'Scenario-3(2)'[Date]=MAX('Scenario-3(2)'[Date]),"Consider" ) In scenario -3(3), please try following DAX: Expected Result = SWITCH( TRUE(), COUNTROWS(FILTER('Scenario-3(3)','Scenario-3(3)'[Direction]="Inbound"))=0,"Not Consider" ) Best Regards, Yadong Fang If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

karthickpbi

Helper I

4 years ago

Solved

Power BI Report

Scenario -1

ID	Date	Name	Direction	Expected Result
1	Jul-04	Agent	Outbound	Consider
1	Jul-04	Customer A	Inbound
1	Jul-04	Agent	Outbound	Consider
1	Jul-04	Customer A	Inbound

In Scenario 1 , we need to consider One Inbound and One outbound Respectuly.

Scenario-2

ID	Date	Name	Direction	Expected Result
1	Jul-04	Agent	Outbound	consider
1	Jul-04	Customer A	Inbound
1	Jul-04	Customer A	Inbound
1	Jul-04	Customer A	Inbound
1	Jul-04	Agent	Outbound	Not consider

In Scenario 2, if we have Multiple Inbound We nee to consider only One bound for Respective One Outbound, other wise no need to consider outbound

Scenario-3

ID	Date	Name	Direction	Expected Result
1	06-07-2004	Agent	Outbound	Consider
1	06-07-2004	Customer A	Inbound
1	06-07-2004	Customer A	Inbound
1	05-07-2004	Customer A	Inbound
1	04-07-2004	Agent	Outbound	Not consider
1	04-07-2004	Agent	Outbound	Not consider
1	04-07-2004	Agent	Outbound	Not consider
1	04-07-2004	Customer A	Outbound	Consider
1	03-07-2004	Customer A	Inbound
1	03-07-2004	Customer A	Outbound	Not Consider

ID	Date	Name	Direction	Expected Result
1	04-07-2004	Agent	Outbound	Consider
1	03-07-2004	Customer A	Inbound
1	02-07-2004	Customer A	Inbound
1	01-07-2004	Customer A	Inbound

ID	Date	Name	direction	Expected Result
1	04-07-2004	Customer A	Outbound	Not consider
1	03-07-2004	Customer A	Outbound	Not consider
1	02-07-2004	Customer A	Outbound	Not consider
1	01-07-2004	Customer A	Outbound	Not consider

If we need to consider outbound if we have a Inbound in below record , also if we have multiple Inbound and Multiple outbound,

here we need to consider one outbound for respective one inbound.

Could you Please check and share us the solution

Thanks in Advance.

v-yadongf-msft

4 years ago

Hi karthickpbi ,

In scenario -1, please try following DAX:

Expected Result = SWITCH(
    TRUE(),
    'Scenario -1'[Name]="Agent","Consider"
    )

In scenario -2, Please create an index column from 1 to 5 first, then try following DAX:

Expected Result = SWITCH(
    TRUE(),
    'Scenario-2'[Name] = "Agent" && 'Scenario-2'[Index] = MIN('Scenario-2'[Index]),"Consider",
    'Scenario-2'[Name] = "Agent" && 'Scenario-2'[Index] > MIN('Scenario-2'[Index]),"Not Consider"
)

In scenario -3(1), please try following DAX:

Expected Result = SWITCH(
    TRUE(),
    'Scenario-3(1)'[Direction]="Inbound",BLANK(),
    'Scenario-3(1)'[Name]= "Agent" && 'Scenario-3(1)'[Date]=MAX('Scenario-3(1)'[Date]),"Consider",
    'Scenario-3(1)'[Name]= "Agent" && 'Scenario-3(1)'[Date]<MAX('Scenario-3(1)'[Date]),"Not Consider",
    'Scenario-3(1)'[Name]= "Customer A" && 'Scenario-3(1)'[Date]= MIN('Scenario-3(1)'[Date]),"Not Consider",
    'Scenario-3(1)'[Name]= "Customer A" && 'Scenario-3(1)'[Date]> MIN('Scenario-3(1)'[Date]),"Consider"
)

In scenario -3(2), please try following DAX:

Expected Result1 = SWITCH(
    TRUE(),
    'Scenario-3(2)'[Name]= "Agent" && 'Scenario-3(2)'[Date]=MAX('Scenario-3(2)'[Date]),"Consider"
)

In scenario -3(3), please try following DAX:

Expected Result = SWITCH(
    TRUE(),
    COUNTROWS(FILTER('Scenario-3(3)','Scenario-3(3)'[Direction]="Inbound"))=0,"Not Consider"
)

^{Best Regards,}

^{Yadong Fang}

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

Power BI Report.pbix115 KB

3 Replies

v-yadongf-msft

Community Support

4 years ago

Hi karthickpbi ,

In scenario -1, please try following DAX:

Expected Result = SWITCH(
    TRUE(),
    'Scenario -1'[Name]="Agent","Consider"
    )

In scenario -2, Please create an index column from 1 to 5 first, then try following DAX:

Expected Result = SWITCH(
    TRUE(),
    'Scenario-2'[Name] = "Agent" && 'Scenario-2'[Index] = MIN('Scenario-2'[Index]),"Consider",
    'Scenario-2'[Name] = "Agent" && 'Scenario-2'[Index] > MIN('Scenario-2'[Index]),"Not Consider"
)

In scenario -3(1), please try following DAX:

Expected Result = SWITCH(
    TRUE(),
    'Scenario-3(1)'[Direction]="Inbound",BLANK(),
    'Scenario-3(1)'[Name]= "Agent" && 'Scenario-3(1)'[Date]=MAX('Scenario-3(1)'[Date]),"Consider",
    'Scenario-3(1)'[Name]= "Agent" && 'Scenario-3(1)'[Date]<MAX('Scenario-3(1)'[Date]),"Not Consider",
    'Scenario-3(1)'[Name]= "Customer A" && 'Scenario-3(1)'[Date]= MIN('Scenario-3(1)'[Date]),"Not Consider",
    'Scenario-3(1)'[Name]= "Customer A" && 'Scenario-3(1)'[Date]> MIN('Scenario-3(1)'[Date]),"Consider"
)

In scenario -3(2), please try following DAX:

Expected Result1 = SWITCH(
    TRUE(),
    'Scenario-3(2)'[Name]= "Agent" && 'Scenario-3(2)'[Date]=MAX('Scenario-3(2)'[Date]),"Consider"
)

In scenario -3(3), please try following DAX:

Expected Result = SWITCH(
    TRUE(),
    COUNTROWS(FILTER('Scenario-3(3)','Scenario-3(3)'[Direction]="Inbound"))=0,"Not Consider"
)

^{Best Regards,}

^{Yadong Fang}

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

Power BI Report.pbix115 KB

karthickpbi
Helper I
4 years ago
v-yadongf-msft ,

I Implemented the scenario and in some come another scearion is coming up with this
karthickpbi
Helper I
4 years ago
HI v-yadongf-msft ,

Thanks for your help, the solution is working perfectly.

Forum Discussion

Power BI Report

3 Replies

Related content

Image display in Power bi report

Error loading Power BI report

Power BI Report Builder: Cannot access report parameters inside Power Query (M language)

Power BI Matrix report

Power BI Report Builder