Help on Dax logic affecting only for one context value not impacting others

Hi All,

Here is the Requirement.

I have two sources coming from excel spreadsheet (Dimension) and Oracle database(Fact).

excel spreadsheet has two sheet one has capability information and another sheet has facility group,

oracle has two views OAR_DLVRY_FCLTY_ALCTN_V;OAR_SHPR_ALCTN_V.

i have a Matrix table data coming from both spreadsheet and view( OAR_DLVRY_FCLTY_ALCTN_V) relationship is many to many

but there is this logic based on Facility group.(Grouping Should be done with Delivery facility code and Remote facility)i thought i can merge two spreed sheet into one and create custom column but my numbers doesn’t look correct. and this Facility Group should be used in my Matrix table containing data from both excel and View.

shows double the volume. so i guess it should be done by other means or using dax.

here logic:

if[Facility Code] =“CHICAPPL” and [Remote facility] = “CHICAPPL” then “CHICAPPL”

else
if[Facility Code] =“CHICAPPL” or [Remote facility] = “PDVMWLT” then “PDVMWLT”
and this applies to only for this one “Chicap” and other should remain same.

and remote facility use is only for this condition only for chicap and it shouldn’t not impact other. but when i try to merge between two spreadsheets(capability / facility group) in order to get remote facility
my numbers are not right showing double the volume.

if i create dax calculated column numbers are also not right me something wrong with the relationships…

calculated column:

if[Facility Code] =“CHICAPPL” && [Remote facility] = “CHICAPPL” , “CHICAPPL”

else
if[Facility Code] =“CHICAPPL” || [Remote facility] = “PDVMWLT” , “PDVMWLT” , " [Facility Code] "

Delivery Feeder Capability.xlsx (22.3 KB)
Nomination_Test.pbix (3.4 MB)

how can we deal with such requirement?

Hi @Priya, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Hi @Priya

I am not sure about the requirement but based on description, I have created a new Table by performing Merge b/w Delivery_Feeder Capability and Facility Grouping in Power Query.

New table will have same records as Delivery_Feeder Capability except for CHICAPPL that will have two rows one for CHICAPPL and other for PDVMWLT.

Below is the PQ

let
    Source = Table.NestedJoin(#"Delivery_Feeder Capability_source", {"Facility Code"}, #"Facility Grouping", {"Delivery Facility"}, "Facility Grouping", JoinKind.LeftOuter),
    #"Expanded Facility Grouping" = Table.ExpandTableColumn(Source, "Facility Grouping", {"Remote Facility"}, {"Remote Facility"}),
    Custom1 = Table.AddColumn(#"Expanded Facility Grouping","Facility Code1",each if [Facility Code] = "CHICAPPL" and [Remote Facility] = "CHICAPPL" then "CHICAPPL" else if [Facility Code] = "CHICAPPL" and [Remote Facility] = "PDVMWLT" then "PDVMWLT" else [Facility Code]),
    #"Removed Columns" = Table.RemoveColumns(Custom1,{"Facility Code", "Remote Facility"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Facility Code1", "Facility Code"}}),
    #"Removed Duplicates" = Table.Distinct(#"Renamed Columns")
in
    #"Removed Duplicates"

Please check and let me know if this is helpful or other changes that may be required.

EDNA_Nomination_Test_Solution.pbix (3.4 MB)

Thanks
Ankit J

Hi @Priya, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!