Forum Discussion

Cobalt2234's avatar
Cobalt2234
Regular Visitor
1 year ago
Solved

demultiplying rows on a table based on a specific filtered value

Hello,

The question is not easy to formulate, so, let's take an example :

 

Lets imagine that I have these 2 tables :

 

DIMTABLE :

Service_CDApplication_CD
100766832
1007610314
1007610337
100785330
100786832

 

AND ANOTHER TABLE : FACT_TABLE :

Application_CDDATEVAL1INFDESC
683201.oct.241afds
683202.oct.242bsf
683203.oct.243csfddf
533002.oct.244asgfsd
533003.oct.245bsf
533001.oct.246csdf
1033702.oct.247afds
1033703.oct.248bfs
1033701.oct.249cfds
1031402.oct.2410asdf
1031403.oct.2411bdfssfd
1031401.oct.2412csdfsdfsd

 

I need to mix these 2 tables (Sort of Crossjoin ?) and get the below result table :

 

Service_CDApplication_CDDATEVAL1INFDESC
10076683201.oct.241afds
10076683202.oct.242bsf
10076683203.oct.243csfddf
100761031402.oct.2410asdf
100761031403.oct.2411bdfssfd
100761031401.oct.2412csdfsdfsd
100761033702.oct.247afds
100761033703.oct.248bfs
100761033701.oct.249cfds
10078533002.oct.244asgfsd
10078533003.oct.245bsf
10078533001.oct.246csdf
10078683201.oct.241afds
10078683202.oct.242bsf
10078683203.oct.243csfddf

 

 

If it is possible to do that in an easy way with DAX, it would help a lot,

 

Thanks,

Roland.

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Cobalt2234 

     

    Here are 2 workarounds for your reference:

    1:

    Click Transform data to go to power query.

    First of all, Duplicate the DIMTABLE(Another table will do the same).

    Click the Merge Queries in the Home pane:

    Select the Application_CD columns and click OK.

    Expand the table:

    The result is as follow:

     

     

    2:

    Add a calculated table:

    Table = 
	SELECTCOLUMNS(
		FILTER(
			CROSSJOIN(
				'DIMTABLE',
				'FACT_TABLE'
			),
			'DIMTABLE'[Application_CD] = 'FACT_TABLE'[Application_CD]
		),
		'DIMTABLE'[Service_CD],
		'FACT_TABLE'[Application_CD],
		'FACT_TABLE'[DATE],
		'FACT_TABLE'[VAL1],
		'FACT_TABLE'[INF],
		'FACT_TABLE'[DESC]
	)

    The result is as follow:

     

    Best Regards

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

4 Replies

  • 123abc's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    1 year ago

     

    If you prefer DAX, you can use a calculated table to achieve the result. Here's the DAX formula to create a new table that combines both:

     

    ResultTable =
    SELECTCOLUMNS(
    FILTER(
    CROSSJOIN(DIMTABLE, FACT_TABLE),
    DIMTABLE[Application_CD] = FACT_TABLE[Application_CD]
    ),
    "Service_CD", DIMTABLE[Service_CD],
    "Application_CD", DIMTABLE[Application_CD],
    "DATE", FACT_TABLE[DATE],
    "VAL1", FACT_TABLE[VAL1],
    "INF", FACT_TABLE[INF],
    "DESC", FACT_TABLE[DESC]
    )
     

    Final Output

    After applying either method, you’ll get the combined table as desired:

    Service_CD Application_CD DATE VAL1 INF DESC
    10076683201.oct.241afds
    10076683202.oct.242bsf
    10076683203.oct.243csfddf
    ..................
    • Cobalt2234's avatar
      Cobalt2234
      Regular Visitor
      1 year ago

      Thanks for this Dax solution.

      I decided to use the Power Query solution above that I thought was better for performance issue, the real table is quite big.

      But anyway, I'll keep also your solution provided in my notes.

      Thanks a lot for your time and help provided.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Cobalt2234 

     

    Here are 2 workarounds for your reference:

    1:

    Click Transform data to go to power query.

    First of all, Duplicate the DIMTABLE(Another table will do the same).

    Click the Merge Queries in the Home pane:

    Select the Application_CD columns and click OK.

    Expand the table:

    The result is as follow:

     

     

    2:

    Add a calculated table:

    Table = 
	SELECTCOLUMNS(
		FILTER(
			CROSSJOIN(
				'DIMTABLE',
				'FACT_TABLE'
			),
			'DIMTABLE'[Application_CD] = 'FACT_TABLE'[Application_CD]
		),
		'DIMTABLE'[Service_CD],
		'FACT_TABLE'[Application_CD],
		'FACT_TABLE'[DATE],
		'FACT_TABLE'[VAL1],
		'FACT_TABLE'[INF],
		'FACT_TABLE'[DESC]
	)

    The result is as follow:

     

    Best Regards

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

    • Cobalt2234's avatar
      Cobalt2234
      Regular Visitor
      1 year ago

      Thank you so much for the Power Query Workaround.

      I used it and it is working perfectly.

      I did not used the dax solution (for now), but I'll keep it in mind.