Forum Discussion

Clement_74's avatar
Clement_74
Frequent Visitor
2 years ago
Solved

Measure between tables from measure

Hello, i use 3 tables: table1: which contains 90% of my data. this table contains dates, texte, integer values and many others things. specifically: "target time" on each line (float number)   ...
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Clement_74 ,

     

    We can create two measures.

    Measure2 =
SUMX (
    FILTER (
        SUMMARIZE (
            ALL ( 'dimdate' ),
            'dimdate'[Month txt],
            'dimdate'[YEARS],
            'dimdate'[month],
            "measure", [Measure1]
        ),
        'dimdate'[YEARS] = MAX ( 'dimdate'[YEARS] )
            && 'dimdate'[month] <= MAX ( 'dimdate'[month] )
    ),
    [measure]
)

    Flag = IF(MAX('MyTable'[Month])<>BLANK(),1,0)

    We can create a table.

    MyTable = DATATABLE (
    "Month", STRING,
    "Number", INTEGER,
    {
        {"janvier", 1},
        {"février", 2},
        {"mars", 3},
        {"avril", 4},
        {"mai", 5},
        {"juin", 6},
        {"juillet", 7},
        {"août", 8},
        {"septembre", 9},
        {"octobre", 10},
        {"novembre", 11},
        {"décembre", 12}
    }
)

    Create model relationships.

    Select the Month column of the [MyTable] table and sort by [Number].

    Place the [Month] field of the [MyTable] table instead of the [Month_txt] field of the timetable on the visual object.

    Place [Flag=1] on the screening of the visual object.

     

     

     

    Best Regards,

    Neeko Tang

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

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Clement_74 ,

 

Can you check the model relationship between the date table and the fact table? They should be a one-to-many relationship.

As for sorting the names of the months, you can refer to this:

Solved: Re: Unable to sort Tite based on day starting from... - Microsoft Fabric Community

How to Sort by Month in Power BI - Power Tech Tips

 

If you are still having problems with your measures, you can create a pbix with dummy data to share.

 

Best Regards,

Neeko Tang

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

Clement_74's avatar
Clement_74
Frequent Visitor
2 years ago

 Hello, ok i have a dummy pbix to sahre! but how and where can i put it on this forum?

I don't see any option to give the file in the message.

best regards

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Clement_74 ,

         

        We can create two measures.

        Measure2 =
SUMX (
    FILTER (
        SUMMARIZE (
            ALL ( 'dimdate' ),
            'dimdate'[Month txt],
            'dimdate'[YEARS],
            'dimdate'[month],
            "measure", [Measure1]
        ),
        'dimdate'[YEARS] = MAX ( 'dimdate'[YEARS] )
            && 'dimdate'[month] <= MAX ( 'dimdate'[month] )
    ),
    [measure]
)

        Flag = IF(MAX('MyTable'[Month])<>BLANK(),1,0)

        We can create a table.

        MyTable = DATATABLE (
    "Month", STRING,
    "Number", INTEGER,
    {
        {"janvier", 1},
        {"février", 2},
        {"mars", 3},
        {"avril", 4},
        {"mai", 5},
        {"juin", 6},
        {"juillet", 7},
        {"août", 8},
        {"septembre", 9},
        {"octobre", 10},
        {"novembre", 11},
        {"décembre", 12}
    }
)

        Create model relationships.

        Select the Month column of the [MyTable] table and sort by [Number].

        Place the [Month] field of the [MyTable] table instead of the [Month_txt] field of the timetable on the visual object.

        Place [Flag=1] on the screening of the visual object.

         

         

         

        Best Regards,

        Neeko Tang

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